Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gpark
Frequent Visitor

Time Intelligence - compare to fixed year

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

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

2 REPLIES 2
gpark
Frequent Visitor

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
Super User
Super User

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors