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?

[PaulDBrown]

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

[Jdokken]

Thanks @PaulDBrown , 

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

[PaulDBrown]

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)

[Jdokken]

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

[TheoC]

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

[PaulDBrown]

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 

[TheoC]

Legend! Cheers for this mate.

[PaulDBrown]

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

[Jdokken]

  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

[PaulDBrown]

Try keeping them as separate measures

[TheoC]

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

Create four simple measuers:

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

[Jdokken]

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

[TheoC]

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

[Ashish_Mathur]

Hi,

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

[Jdokken]

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. 

[Ashish_Mathur]

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.

[TheoC]

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

Create four simple measuers:

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

[TheoC]

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.

[Jdokken]

In order to get Monthly Revenue I have to take each periods ATD Revenue and subtract it from the previous period. 

[Jdokken]