Quarter Var with Date Table (Fiscal Year is November)

DAX DAX Calculations
1

My Fiscal year Starts November. I always use a date table that calls this out.

I am not sure how to calculate the Quarterly Variance using the date table because the Quarter&Month is not actually a date field. If I use the proper date field it does not recognize November as the start of my Fiscal year.

I’m looking to add both the Fiscal and Monthly Variances, then use the new parameter feature released last month to toggle between both.

PBX attached
eDNA Quarter Var for Non Standard Dates.pbix (131.3 KB)

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
3

Works great…
Do you by any chance have a new Date Calendar Code that includes the FQ Offset? This would save the first two steps.

4

That’s great to hear @chad.sharpe.

No, I don’t have a date table M function that includes this piece of code because you only require it when your FQ isn’t in sync with regular quarters… however it shouldn’t be too difficult to transfer.

You can also take a look at the Power Query series on the eDNA YouTube Channel/ Learning portal I’ve done a couple of video’s that illustrate making modifications to the code.

Hope this is helpful.

1 Like