Forum Discussion

Mo-PHAT's avatar
Mo-PHAT
Helper I
4 years ago
Solved

Variable financial year

Hi,

 

My company uses accounting periods that end on the last Friday of the month.  This means that the start of the financial year is variable. Eg. This financial year (2022) began on the 26/6/2021 and financial year (2023) will begin on the 25/6/2021.

 

When writing YTD calculations, I have to input a string to define the end of the financial year eg

TOTALYTD([Actuals],DimDateandPeriod[Date],ALL(DimDateandPeriod[Date]),"6/30")
but this then puts the calcuation off by a few days.
 
I have tried to make a dynamic column to insert instead of "6/30" but the YTD function will not accept it.  I have a column in my date table which holds the first day of the financial year and tried doing a calculate between dates of that date and today but that doesn't work either.
 
Does anyone have any other ideas of how to get around this problem?  Is there a long hand way of writing YTD where I can have dynamic dates?
DAX
Financial Year
YTD
  • amitchandak's avatar
    amitchandak
    4 years ago

    Mo-PHAT , You need two things for YTD

    Year and Day of year.

    I am assuming you have logic for you start date, based on that You can create year. Numeric year.

    Else create a rank on Start date of year

     

    Year Rank = Rankx('Date', 'Date'[Start of Year],,asc,dense)

    Day of year = Datediff([Start of year], [Date], day())

     

    YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
    LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    https://www.youtube.com/watch?v=km41KfM_0uA

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Mo-PHAT , You need two things for YTD

    Year and Day of year.

    I am assuming you have logic for you start date, based on that You can create year. Numeric year.

    Else create a rank on Start date of year

     

    Year Rank = Rankx('Date', 'Date'[Start of Year],,asc,dense)

    Day of year = Datediff([Start of year], [Date], day())

     

    YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
    LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    https://www.youtube.com/watch?v=km41KfM_0uA