Custom YTD with different start date

[Stoppelaar]

Hi,

I am going nuts trying to find a solution to my qustion, so I hope someone here can helt me!

What I would like to should be simple, I would like to create a YTD TY, YA and 2YA where the starts dates are known but different for each year and I want to have it in my Calendar Table...  

It would look something like this:

YTD TY: 31-01-2021 to MAX Date

YTD YA: 02-02-2020 to MAX Date -1 Year

YDT 2YA: 03-02-2019 to MAX Date -2 Year

Is it possible? 

Many thanks for your help!

[Stoppelaar]

Many thanks @Anonymous ! That worked perfect! 

Now they all 3 work as measures but I need them all in the Calender Table, I have tried some Swith and IF but I cant get it to work at all... any good solution here? 

[Anonymous]

If I got you right you can try something like this:

YTD type column=var varLastDate=MAXX(Periods,Periods[EndOfWeek])

return 

SWITCH(TRUE(),

CalendarTable[Date] in DATESBETWEEN(ALL(CalendarTable[Date]),"01-01-2021",varLastDate), "YTD TY"

CalendarTable[Date] in DATESBETWEEN(ALL(CalendarTable[Date]),"02-02-2020",DATE(YEAR(varLastDate)-1,MONTH(varLastDate)+1,1)-1), "YTD YA"

CalendarTable[Date] in DATESBETWEEN(ALL(CalendarTable[Date]),"03-02-2019",DATE(YEAR(varLastDate)-2,MONTH(varLastDate)+1,1)-1), "YTD 2YA")

[Stoppelaar]

Many thanks @Anonymous ! 

I get a strange error saying "DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument."

EndOfWeek and Date are both date... 

YTD Test =

VAR varLastDate=MAXX(Periods,Periods[EndOfWeek])

Return

SWITCH(

TRUE(),

CalendarTable[Date] in DATESBETWEEN(ALL(CalendarTable[Date]),"31-01-2021",varLastDate), "YTD TY",

CalendarTable[Date] in DATESBETWEEN(ALL(CalendarTable[Date]),"02-02-2020",DATE(YEAR(varLastDate)-1,MONTH(varLastDate)+1,1)-1), "YTD YA",

CalendarTable[Date] in DATESBETWEEN(ALL(CalendarTable[Date]),"02-02-2019",DATE(YEAR(varLastDate)-2,MONTH(varLastDate)+1,1)-1), "YTD 2YA"

)

[Anonymous · Accepted solution]

Try to replace ALL(CalendarTable[Date]) with CalendarTable[Date] in all DATESBETWEEN functions.

[Stoppelaar]

Amazing many thanks for all support @Anonymous ! have a great day!

[Stoppelaar]

Hi @Anonymous 

Thanks for quick feedback! 

It solves my YTD TY:

YTD TY test = CALCULATE([Value], DATESBETWEEN(CalendarTable[Date],"31-01-2021",MAXX(Periods,Periods[EndOfWeek])))

But I cant get the YTD YA and 2 YA to work with the same logic... any idea on how to solve that?

Thanks,

[Anonymous]

@Stoppelaar There are several ways. For example, you can use DATE function like this:

YTD YA = var varLastDate=MAXX(Periods,Periods[EndOfWeek])

return CALCULATE([Value], DATESBETWEEN(CalendarTable[Date],"02-02-2020",DATE(YEAR(varLastDate)-1,MONTH(varLastDate)+1,1)-1))

[Anonymous]

@Stoppelaar You can use DATESBETWEEN function where you point out start and end dates for a period.