Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
This may be another alternative:
Measure = TOTALMTD('Table_Ex'[Amount],DATEADD('Table_Ex'[Date],-1,MONTH))
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,
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.
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
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |