Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Jdokken
Helper III
Helper III

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

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?

 

Jdokken_0-1632263470728.png

 

24 REPLIES 24

@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/).

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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.

@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 )

                         )

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@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 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.