cancel
Showing results for
Did you mean:
Highlighted
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

Accepted Solutions
Highlighted
Super User IX

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

@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...

Proud to be a Super User!

Highlighted
Super User IX

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

@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...

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021