Show all hours between start time and end time

[LCrossling]

Hi Power BI Community,

I've got a dataset with buisnesses and their opening and closing times. I want to be able to show a graph (Bar chart) that shows every hour of the day and the count of businesses open during those hours.
Currently I'm only able to show the number of businesses that open or close at the specified time. Any idea who to approach this problem?

Thanks!

[amitchandak · Accepted solution]

@LCrossling , refer if this file can help

https://www.dropbox.com/s/5wo5jhzl488zy69/Minute_between%20Hours.pbix?dl=0

[negi007]

@LCrosslingthere is a simple solution to your problem. You only need to create a calculated column in your dataset. Syntex for that is  Opening_hrs = HOUR(Store_timing[Opening Time]). Once you have hour column in your data, it is easy to plot it on a bar chart as shown in the below picture. Let me know in case this does not work for you.

[v-juanli-msft · Accepted solution]

Hi @LCrossling 

Create a new query in Transform data,

let
    mindate=List.Min(Table[Date]),
    maxdate=List.Max(Table[Date]),
    counts=Duration.Days(maxdate-mindate),
    dates = List.Dates(mindate,counts+1,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each List.DateTimes(#datetime(Date.Year([Date]),Date.Month([Date]),Date.Day([Date]),0,0,0),24,#duration(0,1,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "datetime"}}),
    #"Inserted Time" = Table.AddColumn(#"Renamed Columns1", "Time", each DateTime.Time([datetime]), type time),
    #"Inserted Hour" = Table.AddColumn(#"Inserted Time", "Hour", each Time.Hour([Time]), Int64.Type)
in
    #"Inserted Hour"

Create measures

open counts =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[id] ),
    FILTER (
        'Table',
        'Table'[Open Hour]
            = MAX ( Query1[Hour] )
            && 'Table'[Date]
                = SELECTEDVALUE ( Query1[Date] )
    )
)

close counts =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[id] ),
    FILTER (
        'Table',
        'Table'[Close Hour]
            = MAX ( Query1[Hour] )
            && 'Table'[Date]
                = SELECTEDVALUE ( Query1[Date] )
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@LCrossling , refer if this file can help

https://www.dropbox.com/s/5wo5jhzl488zy69/Minute_between%20Hours.pbix?dl=0