Custom YTD when end of year changes every year

[Anonymous]

I have a company calendar that adjusts year end date on the fly at the end of year, every year. Since I could not figure out a way to use that in calculations I made an excel sheet an manually input the calendar there so as to import it as a date table in my model. So far so good.

But I can't figure out how to write a custom YTD function for this kind of a calendar. Needless to say as I manually enter more dates in this file the custom YTD should work as well...

Since I am using a manual excel sheet as a date table I have no problems in making a Pivot Table filtered on Company Year and Company Month and Company Date - which means the totals in this pivot table will be a YTD. However, I don't know how to make a custom YTD function on this.

https://drive.google.com/file/d/10sLR_nDnHtQ-1OHPVuV8wjplH5toKi8g/view?usp=sharing

That is a link to my date table in excel.

Thanks.

[amitchandak · Accepted solution]

@Anonymous , Try a formula like this for YTD

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

If needed use week number in place of month 

To use day of year in ytd

create columns 

Year Start date =  minx(filter(Date, [Year] =earlier([Year])),[Date])

Day of Year =datediff([Year Start date] , [Date],Day) +1

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))

[Anonymous]

That worked. Thanks!