topic Re: Previous Month from January in DAX Commands and Tips

Previous Month from January

jtma508 — Fri, 07 Jan 2022 21:12:17 GMT

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?

Re: Previous Month from January

AlexisOlson — Fri, 07 Jan 2022 21:15:19 GMT

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?

Re: Previous Month from January

amitchandak — Sat, 08 Jan 2022 03:49:26 GMT

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

Re: Previous Month from January

Anonymous — Wed, 12 Jan 2022 08:59:25 GMT

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