1

I need to have calculated member to calculate running total base on Dim Date that should work with all dates that filtered in reports. most of solutions calculates from first date in Dim Date or Null to Current Member, but I need to calculate from first date that filtered to current member and exclude dates that they are not in filter Sample Image

MoazRub
  • 2,881
  • 2
  • 10
  • 20
sepideh.sp
  • 61
  • 6

1 Answers1

1

Try this:

//capture the dates selected in the where clause of the query
CREATE DYNAMIC SET CURRENTCUBE.SelectedDates as
[Dim Date].[Date].[Date].Members;

CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative Sale Price] as
Sum(
 {
  SelectedDates.Item(0).Item(0)
  :
  Tail(Existing [Dim Date].[Date].[Date].Members, 1).Item(0).Item(0) //capture the last date present in the Filter context for this cell
 },
 [Measures].[Sale Price]
);

CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative Sale Price with Skips] as
Sum(
 Exists(
  SelectedDates,
  {
   SelectedDates.Item(0).Item(0)
   :
   Tail(Existing [Dim Date].[Date].[Date].Members, 1).Item(0).Item(0) //capture the last date present in the Filter context for this cell
  }
 ),
 [Measures].[Sale Price]
);
GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • Thank you so much for your answer, that help me very much but what about the dates that doesn't exist in our interval , if we exclude some dates between first member and current member they will be in our calculate for example if first member is 2017-01 and current member is 2019-10 if we filtered Dim Date for just 2017 and 2019 , all price of 2018 will summarize too – sepideh.sp Jan 21 '19 at 10:32
  • can we have recursive sum in mdx ? if we could sum every member with the Previous member maybe we could solve that,do you know how can have recursive aggregation in mdx ? – sepideh.sp Jan 21 '19 at 10:39
  • @sepideh.sp I added a second measure to my answers. See if that handles the skipped dates like you wanted. – GregGalloway Jan 21 '19 at 13:21
  • Thank you so much for your help, It Worked :) – sepideh.sp Jan 23 '19 at 11:04
  • I have performance problem when use date in DimDate, in YearMonth it's ok but when we detailed to Day it loads for at least 10 second, Can we do some work to improving performance ? – sepideh.sp Jan 26 '19 at 12:07
  • @sepideh.sp what’s the exact shape of your report? Do you just have dates on rows or some other field? – GregGalloway Jan 26 '19 at 12:33
  • I use just date for my chart but it can be filtered by date, product,product group, city and other dimensions, for example in my current report I have date and cumulative price in my chart axis, product and category of products are in my filters – sepideh.sp Jan 26 '19 at 13:08
  • @sepideh.sp hmm. If you use regular sales price is it fast? – GregGalloway Jan 26 '19 at 13:23
  • yes it is very fast, I faced another problem too it dosn't work with power bi!! , it work very well with excel but in power bi it calculate from first date and dosn't filter dates ! that's very strange – sepideh.sp Jan 26 '19 at 13:42
  • @sepideh.sp it is definitely a strange and expensive calculation. Not sure how to make it work with Power BI or make it faster. – GregGalloway Jan 26 '19 at 14:08
  • Yes that's strange not working with Power, thanks for your help – sepideh.sp Jan 27 '19 at 10:25
  • do you know how can I have Calculated member that show first date in my filtered date, that should filter by filtered date but by pass dates that came in rows I need that field show the same value for all dimensions in row – sepideh.sp Mar 02 '19 at 06:53