Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jksl_12
New Member

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, and I would really enjoy if it was possible to include it.

Below I have an example of my data that is put into the table.

 

RegionDate of reportCategoryStorage amount
NorthDecember 1stPencils4.500
SouthDecember 1stPencils6.000
WestDecember 1stPencils7.500
CentralDecember 1stPencils10.500
NorthNovember 1stPencils5.000
SouthNovember 1st Pencils5.000
WestNovember 1stPencils8.000
CentralNovember 1stPencils12.000

 

I would love to be able to set up a formula so that I can see the difference from last month. However, the thing that I would enjoy even more is for this to happen automatically when I refresh my data. On January 1st I am going to be getting new data put into the same table, so I would want it to be compared to the December data instead and see the difference between the values.

So basically what I would want the finished matrix to look like this, and then be able to update it without having to do that manually. So that means next time the change would be calcuated from December 1st and then compared with the new values of January 1st.

RegionStorage amountChange since last
North4.500-500
South6.0001.000
West7.500-500
Central10.500-1.500

I hope someone will be able to help me out, and if there is anything else you need in order to be able to solve my problem, then do let me know!

 

Thanks in advance and kind regards

- J

1 ACCEPTED SOLUTION
Jamie_Scott
Resolver II
Resolver II

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

View solution in original post

2 REPLIES 2
ray_codex
Resolver I
Resolver I

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.

Jamie_Scott
Resolver II
Resolver II

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.