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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mo-PHAT
Helper I
Helper I

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?
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

2 REPLIES 2
Mo-PHAT
Helper I
Helper I

Thanks heaps @amitchandak !!

 

That works a treat 🙂

 

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors