cancel
Showing results 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

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.

 Region Date of report Category Storage amount North December 1st Pencils 4.500 South December 1st Pencils 6.000 West December 1st Pencils 7.500 Central December 1st Pencils 10.500 North November 1st Pencils 5.000 South November 1st Pencils 5.000 West November 1st Pencils 8.000 Central November 1st Pencils 12.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.

 Region Storage amount Change since last North 4.500 -500 South 6.000 1.000 West 7.500 -500 Central 10.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
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

2 REPLIES 2
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.

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

Announcements

#### 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 Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors