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

[OLADAODU]

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

[amitchandak · Accepted solution]

@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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
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...


Appreciate your Kudos.