How to get Rolling 12 revenue while filtering on a...

kjohnanand · ‎03-19-2024

I am trying to get a rolling 12 revenue figure for active clients filterable by quarter and year. My current function uses a Quarter Index to calculate for the past 12 months. Here it is.

Rev R12 12 = 
VAR _lastq = MAX('RCB Core Revenue'[Quarter Index])
VAR _firstq = _lastq - 4
VAR _res = CALCULATE(
SUM('RCB Core Revenue'[Revenue]), 'RCB Core Revenue'[Active vs Deconverted] = "Active",
FILTER(ALL(_QuarterEndTable), '_QuarterEndTable'[Quarter Index] > _firstq && '_QuarterEndTable'[Quarter Index] <= _lastq))
RETURN _res

The basic outline of this seems to work relatively well, but the issue I'm having is that It's adding up the revenue for clients that were active in any/all 4 quarters in the rolling 12 when I only want the active filter to be applied to the max quarter in the function. For example, if I pick Q4 2023, I only want to see clients that were active in that specific quarter rather than clients that were active throughout the entire 12 month period, while still pulling the full rolling 12 revenue. Right now I'm finding that it either gives me the revenue for all active clients in all 4 quarters or it filters to active in the last quarter but only gives me the revenue for that quarter rather than the full Rolling 12.

I figured out that the issue is most likely due to the ALL() function in my calculation, so I was trying to find something that allowed me to filter on multiple variables at once. For example, keep the ALL() but then stipulate that the "active" filter only be applied to the max quarter in the range. Any assistance on this would be appreciated!

v-kongfanf-msft · ‎03-19-2024

Hi @kjohnanand ,

Try to modify your formula like below:

Rev R12 12 = 
VAR _lastq = MAX('RCB Core Revenue'[Quarter Index])
VAR _firstq = _lastq - 4
VAR _maxQuarterActiveClients = CALCULATETABLE(
    VALUES('RCB Core Revenue'[ClientID]),
    'RCB Core Revenue'[Quarter Index] = _lastq,
    'RCB Core Revenue'[Active vs Deconverted] = "Active"
)
VAR _res = CALCULATE(
    SUM('RCB Core Revenue'[Revenue]),
    FILTER(
        ALL('RCB Core Revenue'),
        'RCB Core Revenue'[Quarter Index] > _firstq && 'RCB Core Revenue'[Quarter Index] <= _lastq
    ),
    TREATAS(_maxQuarterActiveClients, 'RCB Core Revenue'[ClientID])
)
RETURN _res

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

kjohnanand · ‎03-20-2024

Thank you for the reply. It looks like I'm getting a rolling 12 now, but the revenue number is higher than it should be. FY 2023 should be around 2.75bn, but I'm getting over 3bn. I think there's ann issue with the active clients filter. Here's my data if that helps.

Sample Data.xlsx