New calculation based on peak hour usage

[Anonymous]

Hi Community!

I need help with develop calculation.

I have data which shows item usage (with datetime).

Mockup:

item_id	calculation result	use	datetime
1	10	1	2019-11-16 12:00:00
1	12	1	2019-11-16 12:15:00
1	6	1	2019-11-16 13:50:00
2	10	1	2019-11-16 08:00:00

Calculation is simple avg.

I also have dim table, which help me define HourOfDay based on datetime column.

I wish to create new calculation, which is per item, but only for peak hours. Peak hour is hour which biggest item usage. 

For item_id=1 peak hour = 12, for item_id = 2 -> 08

I manage to define output table which looks like this:

item_id	sum_usage	HourOfDay
1	2	12
1	1	13
2	1	8

I have final table which looks like this:

item_id	calculation	calculation last 20 days	calculation last 20 days peak hours
1	9	9	should be 11
2	10	10	should be 10

current calculation for last 20 days:

calculation last 20 days =
CALCULATE([calculation];all(Table[datetime]);Table[datetime]>=today()-20)

Should I create some kind of virtual aggregated table to get max peak hour and use it in calculation as new variable?

Regards!

[Icey · Accepted solution]

Hi @Anonymous ,

If I don't misunderstand, you can try this:

Create measures:

Measure = 
VAR CurrentValue = [sum_usage]
VAR MaxValue =
    CALCULATE (
        MAXX ( 'Table', [sum_usage] ),
        ALLEXCEPT ( 'Table', 'Table'[item_id] )
    )
RETURN
    IF ( CurrentValue = MaxValue, 1 )

calculation last 20 days peak hours = 
CALCULATE (
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[calculation result] ),
            FILTER ( 'Table', [Measure] <> BLANK () )
        ),
        CALCULATE ( [sum_usage], FILTER ( 'Table', [Measure] <> BLANK () ) )
    ),
    FILTER ( 'Table', 'Table'[datetime] >= TODAY () - 20 )
)

PBIX file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.