cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Calculate difference from previous month

Hello!

 

I would like to display the difference in cost by month. This is the current table I have. It shows cost by year-moth by site. I would like to show the difference in cost each month in another table. I have tried making a few measures but power BI was not too happy with them. Any help is greatly appreciated!

 

Data Sample.PNG

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Calculate difference from previous month

Hi @mmakhlo,

 

You can try to use below measure to get the diff of current date and previous month.

Diff of Previous Month = 
var currSite=MAX([Site])
var currDate=MAX([Date])
return
SUMX(FILTER(ALL('Sample'),[Site]=currSite&&FORMAT([Date],"yyyy-mm")=FORMAT(currDate,"yyyy-mm")),[Amount])-SUMX(FILTER(ALL('Sample'),[Site]=currSite&&FORMAT([Date],"yyyy-mm")=FORMAT(DATE(YEAR(currDate),MONTH(currDate)-1,DAY(currDate)),"yyyy-mm")),[Amount])

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User IV
Super User IV

Re: Calculate difference from previous month

Hi @mmakhlo

 

If I was looking to solve this problem and ensure that I am using best practise as well as be able to use all the native DAX functions I would suggest first creating a Date Table. You can use the link below which explains what a date table is and how to use it.

 

http://radacad.com/do-you-need-a-date-dimension

 

Then once that has been created I would then create a measure which would represent the Previous Month using the PREVIOUSMONTH DAX function.

 

PY - Total Employee Expenses = CALCULATE([Total Employee Expenses],PREVIOUSYEAR('Date'[Calendar Date]))

 

Then it would be as simple as using your [Total Employee Expenses] - [PY - Total Employee Expenses] 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Highlighted
Frequent Visitor

Re: Calculate difference from previous month

Hi @GilbertQ

 

The date table was a great suggestion. It helped fix a few other issues I am having with the report. With regards to this post/calculation, when I create the measure for the cost of the previous month, it doesn't create a value.

 

Here is my equation

 

Ext Cost Previous Month = CALCULATE([Ext Cost Sum], PREVIOUSMONTH('Date'[Date]))

 

When I try to insert the number into a table nothing appears.

 

My value for the difference between months is the same as the current month which is another indication that the previous month calculation is not working properly.

 

Any suggestions?

Highlighted
Super User IV
Super User IV

Re: Calculate difference from previous month

Hi @mmakhlo


Can you confirm that when creating your table or visual that you are using the Month from the DATE table you created and NOT from your data table?

 

That is often the issue when data is not showing, due to the PREVIOUSMONTH using the DATE table, and the table or visual is using the column from the data table, so the PREVIOUSMONTH will not work as expected.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors