Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Since you don't have a Date Table, you "cannot" use Time Intelligence functions. So you need to code the date calculations.
Try:
Previous month value =
VAR SelDate = MAX(Period [Period Start Date])
VAR SelPeriod = YEAR(SelDate) * 100 + MONTH(SelDate)
VAR PP = IF( MONTH(SelDate) =1, (YEAR(SelDate) -1) * 100 + 12, YEAR(SelDate) * 100 + MONTH(SelDate) -1
RETURN
CALCULATE([ATD Revenue], FILTER(ALL(Period), SelPeriod = PP))
and
-Monthly Revenue = IF(ISBLANK([Previous month value]), BLANK(), [ATD Revenue] - [Previous month value])
PS. Apologies for any syntax errors. I'm typing on my phone
Proud to be a Super User!
Paul on Linkedin.
Is Period[Period Start Date] type Date?
Also, please refer to my next post where I correct the code to deliver the right result (you can see by the image included in the post)
Proud to be a Super User!
Paul on Linkedin.
Okay, I'll tried them as separate measures. I'm still getting these random blanks and I don't get it. So i'm setting up time with someone in IT who can perhaps help me figure out what's going on in the cube. I thought it was interesting in my -PreviousMonth2 formula that the blanks line up with the odd numbers that are showing up "421", 100019" and "38420". Maybe that has something to do with it.
@PaulDBrown - I had no idea about the Time Intelligence functions not being able to be used without a Date table. That's a great tip, thank you for sharing.
I tried to generate your code. The only syntax error that popped up was the ")" missing after -1 prior to the "RETURN". The only other thing for @Jdokken to note is Monthly Revenue already exists so may be worth adjusting the name to Monthly Revenue $ or something else.
@PaulDBrown it is probably an error on my part, however, for some reason, the code was only returning blanks for both Monthly Revenue and Previous Month Value. (Unrelated topic, your profile image is absolutely awesome! It's always listed in Top Kudoed Authors and it's the first one to catch the eye! Gold mate!)
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
Here is a Microsoft reference on the need for a Date Table for Time Intelligence functions:
https://docs.microsoft.com/en-gb/learn/modules/dax-power-bi-time-intelligence/2-functions
Proud to be a Super User!
Paul on Linkedin.
Legend! Cheers for this mate.
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
Ok, I've had a chance to test this (the code needs corrections)
First create the measure for the previous period:
Prev Period =
VAR SelDate =
MAX ( 'Table'[Period Start Date] )
VAR PP =
IF (
MONTH ( SelDate ) = 12,
( YEAR ( SelDate ) + 1 ) * 100 + 1,
YEAR ( SelDate ) * 100
+ MONTH ( SelDate ) + 1
)
RETURN
PP
And then
Previous month value =
VAR SelDate =
MAX ( 'Table'[Period Start Date] )
RETURN
CALCULATE (
[ATD Revenue],
FILTER (
ALL ( 'Table' ),
YEAR ( SelDate ) * 100
+ MONTH ( SelDate ) = [Prev Period]
)
)
and
Monthly revenue =
IF (
ISBLANK ( [Previous month value] ),
BLANK (),
[ATD Revenue] - [Previous month value]
)
and you get:
If that doesn't work, can you show a depiction of how the model is set up?
(thanks for your kind words about my profile pick!)
Proud to be a Super User!
Paul on Linkedin.
I got the "PP" portion of your DAX to work (but had to change it to a -1 vs a +1. But when I try returning the PMV it's blank.
Try keeping them as separate measures
Proud to be a Super User!
Paul on Linkedin.
@Jdokken I recreated your table and then created the solution:
Create four simple measuers:
Steps to follow:
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
@TheoC Thanks so much for trying to figure this out. I'm working with a CUBE and so I can only create measures (no columns). I think there may be something else going on that I can't see as the previous month won't populate for random periods even if there are no gaps in the dates. I'm not even sure where to look in the data to figure out why it's showing blanks for previous periods that clearly have an amount.
@Jdokken I haven't tested Paul's new measures but I am going to go out on a limb here and say it's probably on the money. I was curious as to whether Time Intelligence measures could be embedded in variables to go around the limitations as raised by Paul. So below I've put together very basic variables that seemed to work and not miss any months. I don't have your full data set but this is the output below using made up figures:
Measures are:
If you test the above on your data set, that would be awesome. I am really interested to see whether it works or whether Time Intelligence within Variables is limited as well. But as mentioned, looking at the outputs in the screenshot table, it looks to work okay?
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
Hi,
Do you want the result as a calculated column formula or as a measure? Share a PBI file to work with.
I need a measure as I'm connected to a cube. I haven't been able to figure out how to share a PBI file.
Hi,
You will need a Calendar Table which should have a relationship with the Period start date column. Once that is in place, you will have to drag Date from the Calendar table to your visual (instead of Period start date). Write these measures:
ATD rev = sum(Data[ATD revenue])
Monthly rev = [ATD rev]-calculate([ATD rev],previousmonth(calendar[date]))
Hope this helps.
@Jdokken I recreated your table and then created the solution:
Create four simple measuers:
Steps to follow:
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
Hi @Jdokken
I'd recommend creating a couple of measures such as below:
Current Mth Rev $ = CALCULATE ( [ATD Revenue] , DATESMTD ( 'Period'[Period Start Date] )
Prior Mth Rev $ = CALCULATE ( [ATD Revenue] , DATESADD ( 'Period'[Period Start Date] , -1 , MONTH )
Hope this helps mate.
Give us a high five if it does.
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
In order to get Monthly Revenue I have to take each periods ATD Revenue and subtract it from the previous period.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
108 | |
98 | |
39 | |
30 |