Missing Hours of the Day

[keboon]

Hi All:

I am trying to tell Power Bi to find the missing Hours of the Day for Time Scheduling Ads.  As you can see there is a gap between 4-8am on Sunday.  Is there a formula or process to tell Power Bi that Sunday 4-8 am is the missing time?

Day	Start Hour	End Hour
Sunday	0	4
Sunday	8	12
Sunday	12	16
Sunday	16	20
Sunday	20	24

[v-cgao-msft · Accepted solution]

Hi @keboon ,

Please try:

Column = 
VAR _end =
    CALCULATE (
        MAX ( 'Table'[End Hour] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Day] = EARLIER ( 'Table'[Day] )
                && 'Table'[Start Hour] < EARLIER ( 'Table'[Start Hour] )
        )
    )
VAR _result =
    IF ( 'Table'[Start Hour] <> _end, 'Table'[Start Hour] - _end )
RETURN
    _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[keboon]

Thank you! Now suppose I have multiple dates like Sunday, Monday, Tuesday, etc and multiple missing times.  How does the formula changes to accommodate the different dates and times?

[Greg_Deckler]

@keboon Try:

Measure = 
  VAR __Day = MAX('Table'[Day])
  VAR __Start = MAX('Table'[Start Hour])
  VAR __End = MAX('Table'[End Hour])
  VAR __StartHours = SELECTCOLUMNS('Table',"__Start",[Start Hour])
  VAR __EndHours = SELECTCOLUMNS('Table',"__End",[End Hour])
  VAR __MissingEnd = IF(__End IN __StartHours, BLANK(), __End)
  VAR __MissingStart = IF(__Start IN __EndHours, BLANK(), __Start)
  VAR __Result = IF( __MissingEnd = BLANK() && __MissingStart = BLANK(), BLANK(), __Day & " " & __MissingEnd & "-" & __MissingStart)
RETURN
  __Result

[keboon]

Thank you! Now suppose I have multiple dates like Sunday, Monday, Tuesday, etc and multiple missing times.  How does the formula changes to accommodate the different dates and times?