Work around when using PREVIOUSMONTH and there is a gap in dates

[Jdokken]

Hi- In order to get the Monthly Revenue I need to take the Current Month's revenue and subtract it from the Previous Month's revenue.

This calculation ( -Monthly Rev = [ATD Revenue] - CALCULATE([ATD Revenue],PREVIOUSMONTH(Period[Period Start Date]))  )  works great until there is a gap in the GL Period and then it subtracts that amount from zero which I don't want it to do. It is also not working correctly from Dec to Jan since it is also bringing back the ATD Revenue from that month vs subtracting it from the previous month.

The orange column is what's it's bringing back and the "Should be" column is what I'd want it to return. I'm thinking the workaround may be wrapping this formula in an IF function?

[TheoC]

@Jdokken a few questions:

1. How is your Date column formatted (i.e. Date, DateTime, etc)? 
2. Have you created a "Date table" or are you using the Date field in the existing table as the basis?

If you do not have a Date table, I would recommend adding one in to your model and create a one to many relationship from the Date table to the existing table (https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/).

[Jdokken]

I'll check with IT to see if they created a dates table in the cube. I believe the Period Start Date is a date field in the existing database. 
What's odd is that the PreviousMonth function isn't working properly between New Years, i.e December 2018 to January 2019.

[TheoC]

@Jdokken - okay, if it is in the exisitng database and there's no separate Date table / entity, it is likely the cause of the error.  In almost all instances, most time/date intelligence functions rely on a Date (or Time) table.

With the above  in mind, try give PARALLELPERIOD a run and see if that can achieve the outcome without a specific Date table (you may need to adjust below measure to match fields etc).

Last Month =

     CALCULATE (
          SUM ( [ATD Revenue ) ,
               PARALLELPERIOD ( 'Period'[Period Start Date], -1 , MONTH )

                         )

[TheoC]

@Jdokken definitely to do with the Date table mate (https://community.powerbi.com/t5/DAX-Commands-and-Tips/DATEADD-quot-expects-a-contiguous-selection-w...). Hope this helps 🙂