Previous Month from January

[jtma508]

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:

Sales Prev MO = IF(MONTH([LDate])>1,CALCULATE([Total Sales], PREVIOUSMONTH(Dates[sDate])) ,CALCULATE([Total Sales],*** nothing I've tried here works.

I've tried PARALLELPERIOD, DATEADD, and a hail Mary combining PREVIOUSYEAR with PREVIOUSMONTH.  

Any sugestions?

[Anonymous · Accepted solution]

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

[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-bd52912a5b...

[AlexisOlson]

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?