Same Period Last Year for Custom Fiscal

DAX DAX Calculations
4

Hi @chad.sharpe,

Don’t know what your Date table looks like but I presume it has a Fiscal Year attribute.

TOTALYTD FY = 
VAR MaxDate = MAX( Dates[Date] )
VAR FiscalYear = MAX( Dates[Fiscal Year] )
VAR AllSalesDates =
    ADDCOLUMNS(
        CALCULATETABLE(
            SUMMARIZE( Sales, Dates[Date], Dates[Fiscal Year] ),
            REMOVEFILTERS ( Sales )
        ),
        "@Sales", [Total Sales]
    )
VAR ListDates =
    FILTER( AllSalesDates,
        Dates[Date] <= MaxDate &&
        Dates[Fiscal Year] = FiscalYear
    )
VAR Result = SUMX ( ListDates, [@Sales] )
RETURN

    Result

and for previous year

TOTALYTD LFY = 
CALCULATE( [TOTALYTD FY],
    DATEADD( Dates[Date], -1, YEAR )
)

To stop dates projecting forward I use an IsAfterToday filter on Report/Page or visual level.
Here’s the M code for the Date table I use.

I hope this is helpful

1 Like