Forum Discussion
Helper ICumulative value for each month
Hi Matej,
The best practice would be to create a calendar table and let your users choose which month they'd like to see. If you want only the last month from your dataset and don't want new tables whatsoever, you could modify the measure like this:
And in plain text:
Measure = VAR MaxDate = MAXX ( ALL ( data ), [Date] ) VAR CurrentDate = MAX ( [Date] ) VAR CurrentMonth = MONTH ( MaxDate ) VAR CurrentYear = YEAR ( MaxDate ) VAR Res = SUMX ( FILTER ( ALL ( data ), [Date] >= DATE ( CurrentYear, CurrentMonth, 1) && [Date] <= CurrentDate ), [Value] ) RETURN IF ( FORMAT ( CurrentDate, "YYYYMM" ) = FORMAT ( MaxDate, "YYYYMM" ), Res, BLANK () )Best Regards,
Alexander
Helper IHi barritown
Thanks a lot for you time and help, realy appreciate it.
The measure works as intended. Do you know if there is a way of adjusting the measure so when placed in the visual it only displays current month? I would realy like to avoid a situation when the visual is operated by the slicer which would have to be moved manually by one day every day (and to the beging of the month when month starts).
Many thanks
Solution SageHi Matej,
The best practice would be to create a calendar table and let your users choose which month they'd like to see. If you want only the last month from your dataset and don't want new tables whatsoever, you could modify the measure like this:
And in plain text:
Measure =
VAR MaxDate = MAXX ( ALL ( data ), [Date] )
VAR CurrentDate = MAX ( [Date] )
VAR CurrentMonth = MONTH ( MaxDate )
VAR CurrentYear = YEAR ( MaxDate )
VAR Res = SUMX ( FILTER ( ALL ( data ), [Date] >= DATE ( CurrentYear, CurrentMonth, 1) && [Date] <= CurrentDate ), [Value] )
RETURN IF ( FORMAT ( CurrentDate, "YYYYMM" ) = FORMAT ( MaxDate, "YYYYMM" ), Res, BLANK () )Best Regards,
Alexander
- Matej
This works perfectly, thanks a lot for your time and help!