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

 

 

Prev month.JPG

YearCalenderMonthTotal submission#Previous year submissionPrevious Month ParticipationPrev Month TT TestCurrent Month SubmissionLatest Month Submission Count
2019188168816  88160
2019289018901  89010
2019389038903  89030
2019488588858  88580
2019588348834  88340
2019688418841  88410
2019788478847  88470
2019888658865  88650
2019986198619  86190
20191089578957  89570
20191188798879  88790
20191288618861  88610
2020189368936  89360
2020289948994  89940
2020389768976  89760
2020489238923  89230
2020589168916  89160
2020689108910  89108910

 

 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
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://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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

1 REPLY 1
Highlighted
Super User IX
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://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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

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

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors