Forum Discussion
Month over month difference
Hello,
I am trying to find the difference in values month over month. For example, from 11/30/2021 to 12/31/2021 the difference is 405,906 ( total value usd column 11.30 14,407,600 minus total value usd column 12/31 14,813,506). This value should show up in the 3rd row under column NAV MOM Diff 2.
But instead its showing 14,813,506 which is the same number as column total value usd.
I used the formula below but not sure what i am doing wrong?
NAV MOM Diff 2 = SUM('daily_report (2)'[total value usd])-CALCULATE(SUM('daily_report (2)'[total value usd]),PREVIOUSMONTH('daily_report (2)'[pedate]))
Hi gmasta1129 ,
For this type of problem, you need to create a calendar table and link this table to your factual data table by date. You cannot reference the date of the data table directly in the PREVIOUSMONTH function.
M_result = CALCULATE ( SUM ( 'Table'[total value usd] ), FILTER ( ALL ( 'Table' ), 'Table'[Col_2] = MAX ( 'Table'[Col_2] ) ) ) - CALCULATE ( [M_sum], PREVIOUSMONTH ( Tab_cal[Date] ) )related blog and solution as below:
DAX - PREVIOUSMONTH Function - Power BI Docs
Solved: Error with PREVIOUSMONTH - duplicatedates - Microsoft Power BI Community
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
gmasta1129 You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Hi gmasta1129 ,
For this type of problem, you need to create a calendar table and link this table to your factual data table by date. You cannot reference the date of the data table directly in the PREVIOUSMONTH function.
M_result = CALCULATE ( SUM ( 'Table'[total value usd] ), FILTER ( ALL ( 'Table' ), 'Table'[Col_2] = MAX ( 'Table'[Col_2] ) ) ) - CALCULATE ( [M_sum], PREVIOUSMONTH ( Tab_cal[Date] ) )related blog and solution as below:
DAX - PREVIOUSMONTH Function - Power BI Docs
Solved: Error with PREVIOUSMONTH - duplicatedates - Microsoft Power BI Community
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
