Forum Discussion

jksl_12's avatar
jksl_12
New Member
2 years ago
Solved

Difference in amount since last report date made automatic

Hey there I have run into a problem with the automated report that I am setting up and I am hoping that someone will be able to point me in the right direction. It is the last thing I am missing, ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Assuming that it's refreshed monthly and you have no calendar table

     

    Change since last refresh = 

    VAR PreviousMonthValue = 

    CALCULATE(
        SUM('YourTableName'[Storage Amount]),
        'YourTableName'[Date of Report] = EDATE(MAX(YourTableName'[Date of Report]), -1)
    )

    VAR CurrentMonthValue = 

    CALCULATE(
        SUM('YourTableName'[Storage Amount]),
        'YourTableName'[Date of Report] = MAX(YourTableName'[Date of Report])
    )
    RETURN
    CurrentMonthValue - PreviousMonthValue

    If it's refreshed randomly, you might need to create a 'Last Refreshed' date column in Power Query, and then use the datesbetween function in place of EDATE/Max

ray_codex's avatar
ray_codex
Icon for Resolver II rankResolver II
2 years ago

Assuming you have a date dimension connected (that is set as a date table), you can use DAX time intelligence to compare this. U can slice the current month and use this as a DAX statement:

difference since last month =
SUM ( Yourtable[Storage amount] )
    - CALCULATE (
        SUM ( Yourtable[Storage amount] ),
        PREVIOUSMONTH ( YourDateTable[Date] )
    )

This will substract the previous month amount from the month you filtered, or used as a column/row.