Forum Discussion

Helper I

3 years ago

Solved

Cumulative value for each month

Hey guys, I'm trying to create a measure that would return cumualtive value for each month - starting on first with 0.00 and adding sum of sales amounts every day up to the last day of the month....

Need Help

barritown
3 years ago
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
My YouTube vlog in English
My YouTube vlog in Russian

barritown

Solution Sage

3 years ago

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

My YouTube vlog in English

My YouTube vlog in Russian