cancel
Showing results for
Did you mean:
Frequent Visitor

DAX to calculate/return previous month (month before the last only) value

Hello everyone,

Please can I pick on anyone brain on how to measure to just the month before lastdate from the listed values. I am trying to calculate Month on Month change based on the difference between the last month and the just the month before. For example. I want just to return the value for May 2020 as 8916.  so i can do a proportion % of change to the latest month so that every month when a new month value is added, he will also calculate the MoM change between the lastdate and the previousmonth (only one month).

My DAX test below is not returning the value i expect to see

Prev Month Partc 1 =

Var Previous_Month = MAXX(FILTER(ALL(FactDataP16P), FactDataP16P[MI_Submission_Period]<VALUES(FactDataP16P[MI_Submission_Period])), FactDataP16P[MI_Submission_Period])
RETURN
IF(HASONEVALUE(FactDataP16P[MI_Submission_Period]),
CALCULATE(VALUES(FactDataP16P[MI_Submission_Period]),filter(ALL(FactDataP16P), FactDataP16P[MI_Submission_Period]=Previous_Month))
)

Also this is also returning the cummulative up to the May 2020

Prev Month TT Test = CALCULATE([Total submission],DATEADD(FactDataP16P[MI_Submission_Period],-1,MONTH))

Please I need help to get the total submission to just return the month before the lastest month so I can compare

 Year CalenderMonth Total submission #Previous year submission Previous Month Participation Prev Month TT Test Current Month Submission Latest Month Submission Count 2019 1 8816 8816 8816 0 2019 2 8901 8901 8901 0 2019 3 8903 8903 8903 0 2019 4 8858 8858 8858 0 2019 5 8834 8834 8834 0 2019 6 8841 8841 8841 0 2019 7 8847 8847 8847 0 2019 8 8865 8865 8865 0 2019 9 8619 8619 8619 0 2019 10 8957 8957 8957 0 2019 11 8879 8879 8879 0 2019 12 8861 8861 8861 0 2020 1 8936 8936 8936 0 2020 2 8994 8994 8994 0 2020 3 8976 8976 8976 0 2020 4 8923 8923 8923 0 2020 5 8916 8916 8916 0 2020 6 8910 8910 8910 8910

1 ACCEPTED SOLUTION
Super User

@OLADAODU , you can use time intelligence with date table

``````MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])``````

Try

https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Super User

@OLADAODU , you can use time intelligence with date table

``````MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])``````

Try

https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements

Launching new user group features

Learn how to create your own user groups today!