Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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
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?
@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
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?
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |