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

Get 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

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
PaulDBrown
Community Champion
Community Champion

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thanks @PaulDBrown , 

I'm trying out your formula but then I got this error?

Jdokken_0-1632767152062.png

 

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)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Jdokken_0-1632770513916.png

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. 

 

-PM ATD REV =
VAR SelDate = MAX(Period[Period Start Date])
RETURN
CALCULATE([ATD Revenue],
FILTER(ALL(Period),
YEAR(SelDate) * 100 + MONTH(SelDate) = [-PreviousMonth]))

 

Jdokken_1-1632771233838.png

 

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

 

TheoC_0-1632429454207.png

 

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

PaulDBrown
Community Champion
Community Champion

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 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

PaulDBrown
Community Champion
Community Champion

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:

Result.JPG

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Jdokken_1-1632769100068.png  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. 

-PreviousMonth =
VAR SelDate = MAX(Period[Period Start Date])
VAR PP = IF(
MONTH(SelDate) = 12,
(YEAR(SelDate)+ 1) * 100 + 1,
YEAR(SelDate) * 100 + MONTH(SelDate)-1
)
VAR PMV = CALCULATE([ATD Revenue], FILTER(ALL(Period), YEAR(SelDate) * 100 + MONTH(SelDate) = PP))
RETURN
PP

 

Try keeping them as separate measures





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






TheoC
Super User
Super User

@Jdokken I recreated your table and then created the solution:

 

TheoC_0-1632355324825.png

 

Create four simple measuers:

TheoC_1-1632355371940.png

 

Steps to follow:

  • 1. Sum ATD Revenue = SUM ( Table1[ATD Revenue] )
  • 2. Sum CurMth = CALCULATE ( [1. Sum ATD Revenue] , DATESMTD ( Table1[Period Start Date] ) )
  • 3. Sum LstMth = CALCULATE ( [1. Sum ATD Revenue] , DATEADD ( Table1[Period Start Date] , -1 , MONTH ) )
  • 4. Var CurMth v LstMth = IF ( [3. Sum LstMth] = 0 , 0 , [2. Sum CurMth] - [3. Sum LstMth] )

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_0-1632411805368.png

 

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

 

TheoC_0-1632441973351.png

 

Measures are:

 

TheoC_1-1632441995732.png

TheoC_2-1632442009084.png

TheoC_3-1632442046371.png

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

Ashish_Mathur
Super User
Super User

Hi,

Do you want the result as a calculated column formula or as a measure?  Share a PBI file to work with.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Jdokken I recreated your table and then created the solution:

 

TheoC_0-1632355324825.png

 

Create four simple measuers:

TheoC_1-1632355371940.png

 

Steps to follow:

  • 1. Sum ATD Revenue = SUM ( Table1[ATD Revenue] )
  • 2. Sum CurMth = CALCULATE ( [1. Sum ATD Revenue] , DATESMTD ( Table1[Period Start Date] ) )
  • 3. Sum LstMth = CALCULATE ( [1. Sum ATD Revenue] , DATEADD ( Table1[Period Start Date] , -1 , MONTH ) )
  • 4. Var CurMth v LstMth = IF ( [3. Sum LstMth] = 0 , 0 , [2. Sum CurMth] - [3. Sum LstMth] )
     

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
Super User
Super User

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. 

Jdokken_0-1632319546797.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.