Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 a few questions:
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
104 | |
64 | |
60 |