Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
11-04-2020 20:44 PM
Whenever we have targets, typically, they're at the month or Year level. All are other data like sales purchase is typically at the level. Now we want to analyze both these data together there is a challenge one data is at year level another data is at the day level. To deal with such scenarios we have options of creating a day level table or creating a measure that can give us the Day level target.
Measures need
Daily Target = CLOSINGBALANCEYEAR(sum(Target[Target])/(maxx(Target,DATEDIFF(Target[Start Year],Target[Date],day))+1),'Date'[Date])
Period Target = CLOSINGBALANCEYEAR(sum(Target[Target])/(maxx(Target,DATEDIFF(Target[Start Year],Target[Date],day))+1),'Date'[Date])*(DATEDIFF(min('Date'[Date]),MAX('Date'[Date]),DAY)+1)
YTD Target = CLOSINGBALANCEYEAR(sum(Target[Target])/(maxx(Target,DATEDIFF(Target[Start Year],Target[Date],day))+1),'Date'[Date])*TOTALYTD(count('Date'[Date]),'Date'[Date])
eyJrIjoiMTEwMjA4NzItZWVlYi00MmZhLWJlZjQtZTBiMGQ0NTBhMmU5IiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9
First: Thank you for this. I searched for quite a while for this and you nailed it. Good work and thank you.
Question. The YTD target measure works perfectly when I tried it. I have a strange situation in that my target must be reached by October 1st instead of December 31st. If I change the Target[Date] to 10/31/2022 from 12/31/2022 the measure no longer returns any values. I am using a date table and target table exactly like your example. Is what I want possible?