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
dedelman_clng
Community Champion
Community Champion

Leap years and time intelligence

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

1 ACCEPTED 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? 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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? 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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.