Forum Discussion

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
9 years ago
Solved

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

  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    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? 

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    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?

    • dedelman_clng's avatar
      dedelman_clng
      Icon for Community Champion rankCommunity Champion

      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.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion

        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?