Forum Discussion
Previous Month from January
Not a new issue and shocking that it still hasn't been resolved. I posted this under issues already but if anyone has a workaround that actually works I would be most grateful. If you have a PBI report that provides numbers from the Previous Month, none of the functions PREVIOUSMONTH, PARALLELPERIOD, or DATEADD will return values if you happen to be reporting from the month of January. They all return nothing. It would seem none have been coded to look back to the previous year when the month=January. I've searched for workarounds but so far none have functioned as-advertised.
I had been using a Measure: Sales Prev MO = CALCULATE([Total Sales], PREVIOUSMONTH(Dates[sDate])) which works fine for every month except January. So I attempted to trap for January and tried several permutations of:
Hi jtma508 ,
It works fine on my side.
Sales Prev MO = CALCULATE(SUM('Table'[value]), PREVIOUSMONTH('calendar'[date]))My Power BI Desktop version is December 2021. Try upgrading your application and check again.
Or you could try using below formula:
Sales Prev MO = CALCULATE(SUM('Table'[value]), FILTER(ALLSELECTED('Table'),FORMAT('Table'[date],"YYYYMM") = FORMAT(EDATE(SELECTEDVALUE('calendar'[date]),-1),"YYYYMM")))Best Regards,
Jay
3 Replies
- AlexisOlson
Super User
My first guess would be that you might have a year filter context that's combining with PREVIOUSMONTH resulting in nothing.
Can you provide a simple example or .pbix that we can test against?
- amitchandak
jtma508 , Please check the suggestion from AlexisOlson and also check if your calendar is complete.
1. if the dates are missing in the calendar
2. Do you have timestamp in dates in your fact for Jan. Date join should be on column without timestamp
3. calendar table si marked as date table
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4 Hi jtma508 ,
It works fine on my side.
Sales Prev MO = CALCULATE(SUM('Table'[value]), PREVIOUSMONTH('calendar'[date]))My Power BI Desktop version is December 2021. Try upgrading your application and check again.
Or you could try using below formula:
Sales Prev MO = CALCULATE(SUM('Table'[value]), FILTER(ALLSELECTED('Table'),FORMAT('Table'[date],"YYYYMM") = FORMAT(EDATE(SELECTEDVALUE('calendar'[date]),-1),"YYYYMM")))Best Regards,
Jay
