Trying To Implement Previous Month Totals But It Only Works For One Month

[Georgez96]

I'm trying to compare current month total expenses to the previous month total expenses. I'm using this to calculate that:

previous month total expense = CALCULATE( SUM(Expenses[Amount]),MONTH(Expenses[Date of Transaction])=MONTH(TODAY())-1 , YEAR(Expenses[Date of Transaction])=YEAR(TODAY())) 

This only works when I use the slicer on the right when its sliced by last 1 month from the current date but not a calendar month. Additionally, it also does not work when I use the Month slicer on the left. Note that the Month slicer is from a different calendar table that is already joined to dates from the slicer on the right. I want to use the month slicer so that I can view the previous month totals, not last month from the current date. Screenshots are down below, would appreciate the help. 

[v-yadongf-msft · Accepted solution]

Hi @Georgez96 ,

Please check the relationship between two tables.

This is my test table:

Create a new calendar table:

Table = ADDCOLUMNS(CALENDAR(DATE(2022,7,1),DATE(2022,9,30)),"Month",FORMAT([Date],"mmm"))

Relationship between two tables:

You can use the month slicer to view the previous month totals:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Georgez96]

Furthermore, it seems to work only when I use the slicer from last month to current date(ex: 9/08/2022 - 10/08/2022) but not a calendar month. 

[v-yadongf-msft]

Hi @Georgez96 ,

Can you share with me some screenshots of your data and relationships after hiding sensitive information?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

[Georgez96]

I have a relationship already established between those two columns but perhaps  it has to do with the cardinality?