Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a model that shows running daily uptime (YTD). I am attempting to calculate the year-over-year variance (on a daily basis), but the lack of 29-Feb in 2017 is skewing the numbers. When it jumps from 28-Feb to 01-Mar it is taking away an extra day's worth of data.
TYTDRUDelta = TOTALYTD(sum('Date'[UptimeMin]),'Date'[Date]) - TOTALYTD(sumx(
filter( relatedtable('IT Outages'), 'IT Outages'[Business Impact]="TRUE" ), 'IT Outages'[Downtime] ), 'Date'[Date] )
LYRUpDelta = CALCULATE( [TYTDRUDelta], SAMEPERIODLASTYEAR('Date'[Date]) )
[UpTimeMin] is just 24*60 (number of minutes in a day). So the values I am seeing are:
Date TYTDRUDelta LYRUpDelta 27-Feb-2017 83042 83520 28-Feb-2017 84482 84960 1-Mar-2017 85922 87840
Notice that the gap in LYRUpDelta between 28-Feb and 1-Mar is 2880 (or 2 * 24 *60) while the gap between 27-Feb and 28-Feb is just 1440 (24 * 60). There are no records in 'IT Outages' for 28-Feb-2017 or 1-Mar-2017.
I tried PARALLELPERIOD but that ended up putting nothing but blanks in LYRUpDelta.
How do I deal with the leap day in 2016 ?
Thanks,
David
Solved! Go to Solution.
I'm not sure how you think it is skewed. You had more uptime last year on March 1st than this year because you had an entire extra day last year. That's not skew, that's fact, right?
I guess how you deal with it depends on how you want to deal with it. You have presented what you are getting now. What is it that you want it to look like?
The year-over-year variance is now skewed because of the extra day in 2016 (or missing day in 2017). I need to be able to show the year-over-year variance correctly, which might mean excluding data from Feb 29?. My data set is recent, but I have to imagine others have had similar issues with data from 2012, 2008, etc.
I'm not sure how you think it is skewed. You had more uptime last year on March 1st than this year because you had an entire extra day last year. That's not skew, that's fact, right?
Yes, now that I think about it more, I am looking for percentage year over year rather than absolute time, so the skew would not be as noticable. I just hate doing comparison/math functions on percentages, averages, etc.
Thanks for the help
David
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |