Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

New calculation based on peak hour usage

Hi Community!

I need help with develop calculation.

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

Mockup:

item_idcalculation resultusedatetime
11012019-11-16 12:00:00
11212019-11-16 12:15:00
1612019-11-16 13:50:00
21012019-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_idsum_usageHourOfDay
12

12

11

13

21

8

 

I have final table which looks like this:

item_idcalculationcalculation last 20 dayscalculation last 20 days peak hours
199should be 11 
21010should 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!

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

1 Reply

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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.