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 filter selection.

I tried using

Measure = CALCULATE(SUM(Table_Ex[Amount]),PARALLELPERIOD(Table_Ex[Date],-1,MONTH)) but it doesn't work.

Can anyone pls suggest me how to solve this?

 

Thanks.

 

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

     

6 Replies

  • RDDWH's avatar
    RDDWH
    New Member
    7 years ago

    This may be another alternative:

     

    Measure = TOTALMTD('Table_Ex'[Amount],DATEADD('Table_Ex'[Date],-1,MONTH))

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

    Hi Jane123,

    The date in your source table is continuous? If it is, you can use the DATEADD function. If it still doesn't resolve your issue, please post the sample data for further analysis. 

     

    Best Regards,
    Angelia

    • Jane123's avatar
      Jane123
      Frequent Visitor
      9 years ago

      Hi v-huizhn-msft,

       

      I tried using the Dateadd function and it still didn't work.Iam posting sample data here for reference.

      In my report,I have given two slicers for month and year.Based on that selection,I need to get the aggregate amount for the previous month.Final result should be a KPI comparing current month and previous month values.

       

        Date            Amount         Month     Year

      31-08-2016          10           Aug        2016

      31-08-2016          10           Aug        2016

      31-08-2016          10           Aug        2016

      31-07-2016          5              Jul         2016

      31-07-2016          5              Jul         2016

      31-07-2016          5              Jul         2016

      31-07-2016          5              Jul         2016

      30-06-2016          2              Jun        2016

      30-06-2016          2              Jun        2016

      30-06-2016          2              Jun        2016

      30-06-2016          2              Jun        2016

      30-06-2016          2              Jun         2016

       

       

      Thanks,

      Jane

      • 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