Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |