Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Try to replace ALL(CalendarTable[Date]) with CalendarTable[Date] in all DATESBETWEEN functions.
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?
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."
Try to replace ALL(CalendarTable[Date]) with CalendarTable[Date] in all DATESBETWEEN functions.
Amazing many thanks for all support @Anonymous ! have a great day!
Hi @Anonymous
Thanks for quick feedback!
It solves my YTD TY:
@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))
@Stoppelaar You can use DATESBETWEEN function where you point out start and end dates for a period.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |