Quarter Var with Date Table (Fiscal Year is November)

DAX DAX Calculations
2

Hi @chad.sharpe,

See if this will work for you.
First I’ve added a FQ in FY and FQ Offset column to your Dates table. You can find that here:

Created this measure:

QoQ Var% v2 = 
VAR PrevQ = SELECTEDVALUE( 'Date Table'[FQoffset] ) -1
VAR FirstQ = CALCULATE( MIN('Date Table'[FQoffset]), ALLSELECTED('Date Table' )) -1
VAR LastQ = CALCULATE( MAX('Date Table'[FQoffset]), ALLSELECTED('Date Table' )) -1
VAR PQ = 
    CALCULATE( SUM( 'Sample Table'[Expected Value] ),
        REMOVEFILTERS('Date Table' ),
        'Date Table'[FQoffset] = PrevQ
    )
VAR LQTotal = 
    CALCULATE( SUM( 'Sample Table'[Expected Value] ),
        REMOVEFILTERS('Date Table' ),
        'Date Table'[FQoffset] >= FirstQ &&
        'Date Table'[FQoffset] <= LastQ
    )
VAR Result =
    IF( ISINSCOPE('Date Table'[FQ in FY]) || ISINSCOPE( 'Date Table'[FQoffset] ),
        DIVIDE( SUM( 'Sample Table'[Expected Value] ) - PQ, PQ, 0 ),
        DIVIDE( SUM( 'Sample Table'[Expected Value] ) - LQTotal, LQTotal, 0 )
    )
RETURN
    Result

With this result.

image

Here’s your sample file:
eDNA Quarter Var for Non Standard Dates.pbix (137.7 KB)

I hope this is helpful

1 Like