Cumulative value for each month

[Matej]

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. 

So far I have created a measure that returns cumulative for all of the time my dataset is related to:

TESTCumulative2 =
CALCULATE(
[TESTSalesOrder],
FILTER(
ALL('Transaction Date'),
'Transaction Date'[*Date (trans)] <= MAX('Transaction Date'[*Date (trans)])
)
)

 

Can anyone please advise how to proceed to get only a current month values? Also, my date table goes up to 31/12/2023 (as clearly visible on the top of the graph) - would that cause a problem when measure is specified for each month? 

Any recommendations/advices would be much appreaciated. 

[Matej]

Hi @barritown,

I have created a measure couple month ago based on your advices:

_timeIntelMonthSALES =
VAR CurrentDay = TODAY()
VAR MaxDate = MAX('Transaction Date'[*Date (trans)])
VAR CurrentMonth = MONTH(CurrentDay)
VAR CurrentYear = YEAR(CurrentDay)
VAR FirstDay = DATE(CurrentYear, CurrentMonth, 1)
VAR SO = IF(TODAY() = DATESBETWEEN('Transaction Date'[*Date (trans)], FirstDay, CurrentDay), [_Revenue £], 0)
VAR Res = SUMX(
FILTER(
ALL('Transaction Date'),
'Transaction Date'[*Date (trans)] >= DATE(CurrentYear, CurrentMonth, 1)
&& 'Transaction Date'[*Date (trans)] <= MaxDate
&& 'Transaction Date'[*Date (trans)] <= CurrentDay
),
[_Revenue £]
)
RETURN
IF(FORMAT(MaxDate, "YYYYMM" ) = FORMAT(CurrentDay, "YYYYMM"), Res, BLANK())

I am now trying to get this to return previous month with cumulative [_revenue]. So far, I have managed to get the time intelligence to return previous month but I always lose the cumulative measure while doing it - I always end up with just the total for whole month, it is not spread by days.
Would you be please able to help me to modify this measure to return previous month but still retain the cumulative way of doing it? Basically what I am trying to achieve is comparison of this cumulative month vs previous cumulative month (but I keep just bangign my head agains the wall at the moment).

[barritown]

Hi @Matej,

I wonder if you can create a PBIX file with some mocking data similar to your original dataset, incorporate your measure in it and share the file with me?

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[barritown]

Hi @Matej,

I guess you need something like this:

In plain text for convenience:

Measure = 
VAR CurrentDate = MAX ( [Date] )
VAR CurrentMonth = MONTH ( CurrentDate )
VAR CurrentYear = YEAR ( CurrentDate )
RETURN SUMX ( FILTER ( ALL ( data ), [Date] >= DATE ( CurrentYear, CurrentMonth, 1) && [Date] <= CurrentDate ), [Value] )

 Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Matej]

Hi @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

[barritown · Accepted solution]

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

[Matej]

This works perfectly, thanks a lot for your time and help!