Forum Discussion

jcru1999's avatar
jcru1999
Frequent Visitor
3 years ago

Count continuous dates from a data set without weekends

Hi community, I hope you are doing well; I am trying to count the number of consecutive days employees take vacation. I have a data set with an ID identifier and the date when they took a vacation. ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

jcru1999 Depending on how you want to do it, NETWORKDAYS might get you there or perhaps 2 columns like below. See PBIX attached below signature.

Consequetive = 
    VAR __ID = [Employ ID]
    VAR __Date = [Time Off Date]
    VAR __PrevDate = MAXX(FILTER(ALL('Table'), [Time Off Date] < __Date),[Time Off Date])
    VAR __Diff = ( __Date - __PrevDate ) * 1.
    VAR __Result = 
        SWITCH( TRUE(),
            __Diff = 1, 1,
            WEEKDAY(__Date, 2) = 1 && __Diff = 3, 1,
            0
        )
RETURN
    __Result




Consequetive Days = 
    VAR __Date = [Time Off Date]
    VAR __Next = MINX(FILTER(ALL('Table'), [Time Off Date] > __Date && [Consequetive] = 0),[Time Off Date])
    VAR __NextFinal = IF(__Next = BLANK(), TODAY(), __Next)
    VAR __Table = FILTER(ALL('Table'),[Time Off Date] >= __Date && [Time Off Date] < __NextFinal && [Consequetive] = 1)
    VAR __ConsequetiveDays = COUNTROWS(__Table) + 1
    VAR __Result = IF([Consequetive] = 0, __ConsequetiveDays, BLANK())
RETURN
    __Result