How to calculate the number of days in a discontinuous time period

[v-rzhou-msft]

Scenario:  

In Power BI, we can simply calculate the number of days in a consecutive days, but-and we usually can't directly calculate the number of days in discontinuous periods. However, this blog will show you how to calculate the number of days in a discontinuous time period.

Sample data:

Detailed steps:  

1.Create a new Table 2.

Table 2 = 
Var table2=SUMMARIZE('Table','Table'[Date],'Table'[Available])
return
FILTER(table2,[Available]=0)

2.The new calculated column judges the first day of a continuous date.

Judge =
DATEDIFF (
    [Date] - 2,
    MAXX ( FILTER ( 'Table 2', [Date] = EARLIER ( 'Table 2'[Date] ) - 1 ), [Date] ),
    DAY
)
 
 
First day = 
IF([Judge]<>BLANK(),0,1)

3. Calculate the number of days in a consecutive time period.

Consecutive days = 
VAR last_1_date =
    CALCULATE (
        MAX ( 'Table 2'[Date] ),
        FILTER (
            'Table 2',
            'Table 2'[Date] <= EARLIER ( 'Table 2'[Date] )
                && [First day] = 1
        )
    )
VAR sum_ =
    CALCULATE (
        COUNTROWS ( 'Table 2' ),
        FILTER (
            'Table 2',
            'Table 2'[Date] >= last_1_date
                && 'Table 2'[Date] <= EARLIER ( 'Table 2'[Date] )
        )
    )
RETURN
    IF ( [First day] = 1, 1, sum_ )
 
 
 
Days = 
VAR _N1 =
    CALCULATE (
        MIN ( 'Table 2'[Date] ),
        FILTER ( 'Table 2', 'Table 2'[Date] > EARLIER ( 'Table 2'[Date] ) && [First day] = 1 )
    )
VAR _N2 =
    CALCULATE (
        MAX ( 'Table 2'[Consecutive days] ),
        FILTER ( 'Table 2', [Date]>= EARLIER ( 'Table 2'[Date] ) && [Date] <= _N1 )
    )
VAR _N3 =
    CALCULATE (
        MAX ( 'Table 2'[Consecutive days] ),
        FILTER ( 'Table 2', [Date]= MAX ( 'Table 2'[Date] ) )
    )
RETURN
    IF (
        [First day] = 1
            && _N1 <> BLANK (),
        _N2,
        IF ( [First day] = 1 && _N1 = BLANK (), _N3 )
    )

4.Output time range and Table 3.

Time range = 
Var _Start=IF([Consecutive days]=1,[Date])
Var _End=IF([Days]<>BLANK(),_Start+[Days]-1,BLANK())
return
IF(_Start<>BLANK(), _Start&"-"&_End,BLANK())
 
 
Table 3 = 
Var table3=SUMMARIZE('Table 2','Table 2'[Time range],'Table 2'[Days])
return
Filter(table3,[Days]<>BLANK())

Hope this article can help you solve similar problems.   

Author: Tingting Zhang

Reviewer: Kerry Wang & Ula Huang

How to calculate the number of days in a discontinuous time period - Tingting Zhang.pbix