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

Anonymous's avatar
Anonymous
Not applicable
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