Variable financial year

[Mo-PHAT]

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?

[Mo-PHAT]

Thanks heaps @amitchandak !!

That works a treat 🙂

[amitchandak · Accepted solution]

@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