Prior Month Values and % Change for a existing measure which aggregates from daily to monthly

Question

I have a table with daily observations. I have an existing measure (Total_Visits_Sum) that provides the data on a monthly or quarterly basis based on the date I use for the axis. If I use a custom column in the calendar for MMM-YY the data automatically gets aggregated.

SUMMARIZE (
    'Data',
    'Data'[Date]),
    CALCULATE (
    SUM ( 'Data'[Visits])))

My question is how do I get the prior month's value to compare, so I could do a month-over-month change? I have seen the examples that do this but only when the underlying frequency of the data is monthly, not aggregated like I am doing.

Kin Siang Kin Siang · Accepted Answer · 2021-05-29T06:27:45

I have managed to workout a table for the comparison as you mentioned, accept the answer if helping :)

This is my raw data to be present and compared

I create and month year value for reference:

MontyYear = MONTH(Sheet1[Month]) + YEAR(Sheet1[Month])

After month year reference was completed, I can use the value to obtain amount from previous month with this dax measure

prior month value = 
 Var priorM = Sheet1[MontyYear] - 1
return
 CALCULATE(SUM(Sheet1[value]),FILTER(ALL(Sheet1),Sheet1[MontyYear] = priorM),FILTER(ALL(Sheet1),Sheet1[Product] = EARLIER(Sheet1[Product])))

Finally, I use the old value compared with new value with simple formula

Comparison = Sheet1[value] - Sheet1[prior month value]

This is the outcome :)

Prior Month Values and % Change for a existing measure which aggregates from daily to monthly

1 Answers