Forum Discussion

Jane123's avatar
Jane123
Frequent Visitor
9 years ago
Solved

Calculate previous month aggregate amount

Hi   I have a table with the columns Date and Amount.Two filters are provided for the year and month based on the date. I need to calulate the sum(Amount) for the previous month based on the filte...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Jane123，

    I try to reproduce your scenario and get expected result as follows.

    My sample data is shown in the picture below.

     



    Create measure to calculate the total sum sales of previous month.

     

    Previous = CALCULATE(SUM(Table_Ex[Amount]),PREVIOUSMONTH(DateTable[Date]))

    Create slicer including Year, Month field. Create a table displays the expected result. The "Amont" field shows the sum of sales in March, The "PreviousMonth" field shows the sum of sales in Feb.

     



    Best Regards,
    Angelia

     

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi Jane123，

I try to reproduce your scenario and get expected result as follows.

My sample data is shown in the picture below.

 



Create measure to calculate the total sum sales of previous month.

 

Previous = CALCULATE(SUM(Table_Ex[Amount]),PREVIOUSMONTH(DateTable[Date]))

Create slicer including Year, Month field. Create a table displays the expected result. The "Amont" field shows the sum of sales in March, The "PreviousMonth" field shows the sum of sales in Feb.

 



Best Regards,
Angelia

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi,

 

Thank you, but this is not exactly what I need. I have a time period slicer which I cannot change due to business needs.

 

I have an update: 

 

MinusOneMonth = CALCULATE([Count of IDs],FILTER(ALL(Table), 'Date'[MonthNo] = MAX('Date'[MonthNo]) - 1)) 
 
This partly worked. Partly, cause it returns me the amount for the previous month, but taking in account the selected dates. E.g. if I select period from February, 5th, till March, 3rd, it will show me the amount for the period of February, 5th, to February, 28th, while I need to see the amount for the whole February.
 
It also works only if I select some date from previous month. E.g. if I select March, it won't show me the whole amount for February.
 
I tried different variations of ALL and ALLEXCEPT, but apparently it doesn't work as expected. Maybe you could help?
 
Thank you.