Time Intelligence - compare to fixed year

[gpark]

Hi Power BI community!

I am hoping someone can help me with my time intelligence problem.  Due to COVID, we have a requirement to compare results to a fixed year, which for us is our Fiscal 2019 running from July 1, 2018 to June 30, 2019.  I'd like to have MTD and YTD measures that work with my date dimension but are always fixed on that year.

As an example, for my YTD measures I'm using the following:

Turnover YTD CY = 
IF ([IncludeDates],
    CALCULATE(
        [Turnover (Reporting)],
        DATESYTD(DimDate[Date],"6-30")
    )
)

Turnover YTD PY = 
IF([IncludeDates],
    CALCULATE(
        [Turnover YTD CY],
        CALCULATETABLE(
                DATEADD(DimDate[Date],-1,YEAR),
                DimDate[DatesWithInvoicing] = TRUE
    )))

These measures work nicely to show YTD data up to the latest day for both current and previous year for 'apples to apples' comparability.  However, I'm stuck on how to create a 'Turnover YTD PreCovid' measure that will return the data fixed to Fiscal year 2019 but still be otherwise aligned to the date dimension months and days.

Thanks very much!

Cheers,
Grady

[gpark]

Thanks Ibendlin, this makes sense - yes I was trying to do Year over Year to Date all in DAX.  Adding the dimensions to the date table has made this so much simpler, thank you!

[lbendlin · Accepted solution]

We have a similar situation. Our data model includes a custom calendar table for our fiscal year, so it contains the Fiscal Year name which makes filtering easy.

Are you planning to compare against the full period in FY19, or are you planning to do a Year over Year To Date comparison? In that case you need to add a special flag to the FY19 dates to mark the ones that are "in the past"  relative to the last invoice date, and then you add that flag as a filter.