Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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 , 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]) ))
That worked. Thanks!