Time Buckets in Power BI

[Anonymous]

Hi everyone

I have created a new table with "Enter Data" option. I want to use this table for bucketing. Please see the following two tables.

I want to calculate "Total Accidents" by Timeinterval. for example

how many accidents occured in between 07:00 - 12:00 19:00-06:00 and 13:00-18:00

and want to show in a column bar chart.

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

To create a mreasure as below.

Measure = 
VAR mas =
    MAX ( Timeinterval[Starttime] )
VAR mae =
    MAX ( Timeinterval[Endtime] )
RETURN
    IF (
        mas < mae,
        CALCULATE (
            SUM ( trafficAccident[Total Accidents] ),
            FILTER (
                ALL ( trafficAccident ),
                trafficAccident[Accident time] >= mas
                    && trafficAccident[Accident time] <= mae
            )
        ),
        IF (
            mas > mae,
            CALCULATE (
                SUM ( trafficAccident[Total Accidents] ),
                FILTER (
                    ALL ( trafficAccident ),
                    trafficAccident[Accident time] >= mas
                        || trafficAccident[Accident time] <= mae
                )
            )
        )
    )

For more details ,please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[Anonymous]

Hi Frank

THankyou very much for your help. 

I really appreciate that

Regards

Asim/skiper

[Greg_Deckler]

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365