topic Re: Trying To Implement Previous Month Totals But It Only Works For One Month in DAX Commands and Tips

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

Georgez96 — Wed, 05 Oct 2022 21:32:23 GMT

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.

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

v-yadongf-msft — Thu, 06 Oct 2022 02:55:10 GMT

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.

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

Georgez96 — Sat, 08 Oct 2022 16:26:38 GMT

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

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

Georgez96 — Sat, 08 Oct 2022 18:10:19 GMT

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.

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

v-yadongf-msft — Mon, 10 Oct 2022 01:41:28 GMT

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