Custom Groups - Calculating Cumulative Prior Year

[rob.garelick]

All,

I use extensively the Custom Groups in tabular editor to perform all of my time intelligence calculations dynamically across any core measure (SELECTEDMEASURE()). I was even able to do current year cumulative. However I am struggling with calculating cumulative prior year. Custom Groups does have a shortcoming where is cannot perform pure recursion and all the examples of Cumulative Prior Year in the Enterprise DNA teachings/trainings/posts shows this calculation using branching of calculating the prior year first in one measure (e.g. Sales LY) and then using it in the cumulative pattern. I tried a few variations of hacking my current year cumulative formula by performing however DAX has humbled me again. I might be missing something.

I am asking the community to help me figure out the Prior Year Cumulative without using branching or the reliance on another measure. I want this to be used generically across all selected measures (revenue, units, etc.). Below is my DAX for current year cumulative which works fine:

VAR CY = CALCULATE( SELECTEDMEASURE(),
FILTER( ALLSELECTED( ‘Date’[Date] ),
‘Date’[Date] <= MAX( ‘Date’[Date] )
)
)
RETURN
CY

I will try and post the PBIX file to the post.

Thanks,
Rob

[Paul.Gerber]

@rob.garelick hello. I’d be happy to look at it. I would have to look at your Date table. In your measure I don’t see anything referencing PY. A PBIX file would be great.

Talk soon

Paul

[rob.garelick]

Hi Paul.

My calendar is the old DAX generated one leveraging from Alberto Ferrari’s old post years ago. I didn’t realize it does have a Previous Year attribute which I never used. I have been doing the DAX patterns from Enterprise DNA. I’ve attached the zipped PBIX file and it will open to the page that I’m using for confirmation. Current Period Revenue and PY is the first and second column with Cumulative in the third column which all are calculating fine. It’s trying to get the DAX pattern correct for Cumulative PY that I’m struggling due to the short coming of how Custom Groups does not do recursive well.

For instance, I already have an item called PY.
PY =
VAR PY =
CALCULATE (
SELECTEDMEASURE(),
DATEADD(‘Date’[Date],-1,YEAR),
‘Date’[IsPYPast] = TRUE()
)
RETURN
PY

Unfortunately, I cannot take that and branch that into Cumulative PY like so…
VAR PY =
CALCULATE (
SELECTEDMEASURE(),
DATEADD(‘Date’[Date],-1,YEAR),
‘Date’[IsPYPast] = TRUE()
)
VAR CUMULATIVE = CALCULATE( PY,
FILTER( ALLSELECTED( ‘Date’[Date] ),
‘Date’[Date] <= MAX( ‘Date’[Date] )
)
)
RETURN
CUMULATIVE

Attached is my DAX for the Date table…
dax_calendar_generation.txt (66.0 KB)

Also attached is my PBIX file. I had to only include the sales date rows for 2019 Q1 and 2020 Q1 to get around the upload limits.
Transportation Operations Dataset.zip (42.3 MB)

I’ll see if the Previous Year attribute can work but any help writing the Cumulative PY would be very appreciative.

Thanks,
Rob

[Paul.Gerber]

@rob.garelick Hey there. Looking through your file, I think if you use @Melissa Extended Date Table with offsets built into it would help you accomplish your goal. Below is an example of my DAX code using offsets for a report I did for a client. It’s for a moving average though. I am still digging through your file when I have more time.

3 Month MA Offset = 
VAR _currentoffset = SELECTEDVALUE( 'Date'[MonthOffset] )

VAR _MovAvg =
        FILTER ( 
            ALL ('Date'[MonthnYear], 'Date'[Month & Year], 'Date'[MonthOffset] ),
            'Date'[MonthOffset] <= _currentoffset &&
            'Date'[MonthOffset] >= _currentoffset -3 
        )

VAR _VResult =
    AVERAGEX (_MovAvg, [Gross Oil Production])

RETURN _VResult

Here is the link. Time Intelligence series by BrianJ and Melissa

[Paul.Gerber]

@rob.garelick

Is this what you are after?

[EnterpriseDNA]

Hello @rob.garelick

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

[EnterpriseDNA]

Hi @rob.garelick

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!