Custom Groups - Calculating Cumulative Prior Year

Power BI Data Model
5

@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

1 Like