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
Stoppelaar
Frequent Visitor

Custom YTD with different start date

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

8 REPLIES 8
Stoppelaar
Frequent Visitor

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
Not applicable

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")

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
Not applicable

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

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

Stoppelaar
Frequent Visitor

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
Not applicable

@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
Not applicable

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

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.