Forum Discussion

Matej's avatar
Matej
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's avatar
    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

     

Matej's avatar
Matej
Helper I
2 years ago

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).