cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jane123
Frequent Visitor

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.

 

1 ACCEPTED SOLUTION

Hi @Jane123

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

My sample data is shown in the picture below.

 

1.PNG

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.

 

2.PNG

Best Regards,
Angelia

 

View solution in original post

6 REPLIES 6
RDDWH
New Member

This may be another alternative:

 

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

DanielClark
Helper II
Helper II

Hi Jane,
 
Try, Measure = CALCULATE(SUM(Table_Ex[Amount]), PREVIOUSMONTH(Table_Ex[Date])).
 
PREVIOUSMONTH Function (DAX):
https://msdn.microsoft.com/en-us/library/ee634758.aspx
 
This should pull back data from the previous month to the one selected in filters.
 
Regards,

Daniel Clark
Business Insight Data Modeler @ BRIGHTSTARR

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

Hi @Jane123

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

My sample data is shown in the picture below.

 

1.PNG

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.

 

2.PNG

Best Regards,
Angelia

 

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.
v-huizhn-msft
Microsoft
Microsoft

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!