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
Be the first to comment