Forum Discussion

Georgez96's avatar
Georgez96
Frequent Visitor
3 years ago
Solved

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

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's avatar
    v-yadongf-msft
    3 years ago

    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.

4 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      Georgez96
      Frequent Visitor
      3 years ago

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

       

       

    • Georgez96's avatar
      Georgez96
      Frequent Visitor
      3 years ago

      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's avatar
        v-yadongf-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        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