Forum Discussion

hamzashafiq's avatar
hamzashafiq
Icon for Kudo Collector rankKudo Collector
4 years ago
Solved

Formula to Calculate Usage

Dear All Folks,   I need your help to calculate usage in PowerBI as per desired results. My table has four columns Serial Number, Year, Month and Max Hours, I want to calculate the monthly usage ba...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, hamzashafiq 

    You can calculated columns as below:

    YearMonth = DATE('Table'[Year],'Table'[Month],1) 
    Start_date = 
CALCULATE (
    MAX ( 'Table'[YearMonth] ),
    FILTER (
        'Table',
        'Table'[Max Hours] <> BLANK ()
            && 'Table'[YearMonth] <= EARLIER ( 'Table'[YearMonth] )
            && 'Table'[Serial Number] = EARLIER ( 'Table'[Serial Number] )
    )
)
    Diff = 
VAR MaxHours =
    IF (
        'Table'[Max Hours] <> BLANK (),
        CALCULATE (
            MIN ( 'Table'[Max Hours] ),
            FILTER (
                'Table',
                'Table'[Serial Number] = EARLIER ( 'Table'[Serial Number] )
                    && 'Table'[Max Hours] > EARLIER ( 'Table'[Max Hours] )
            )
        ),
        BLANK ()
    )
VAR MinHours = 'Table'[Max Hours]
RETURN
    IF ( MaxHours = BLANK (), BLANK (), MaxHours - MinHours )
    Usage = 
VAR Max_hours =
    LOOKUPVALUE (
        'Table'[Diff],
        'Table'[Serial Number], 'Table'[Serial Number],
        'Table'[YearMonth], 'Table'[Start_date]
    )
VAR Count_month =
    CALCULATE (
        COUNT ( 'Table'[Start_date] ),
        ALLEXCEPT ( 'Table', 'Table'[Serial Number], 'Table'[Start_date] )
    )
RETURN
    Max_hours / Count_month

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

  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, hamzashafiq 

    I may have misunderstood what you meant before. From your last screenshot, the value of some months (3,4,5,6,7,11,12) is missing rather than 'blank'. If so, please check my new attachment. Hope this could help.

     

    Best Regards,
    Community Support Team _ Eason

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi, hamzashafiq 

I may have misunderstood what you meant before. From your last screenshot, the value of some months (3,4,5,6,7,11,12) is missing rather than 'blank'. If so, please check my new attachment. Hope this could help.

 

Best Regards,
Community Support Team _ Eason

hamzashafiq's avatar
hamzashafiq
Icon for Kudo Collector rankKudo Collector
4 years ago

Hey,

 

It's me again, the previous solution you gave was perfect except for the cases when we have null values in "Max-Hours" column or the values become less compared with previous month or row (highlighted in yellow). For these cases we need to replace the values for the previous month Hours (highlighted in purple). Please see the attached screenshot and also the desired input for "Max-Hours" column. 

 

Below is the formula I changed but it only replaces for one row only. 

 

 

 

max_hours2 =
if(ISBLANK(monthly_usage_table[Max-Hours]) ,CALCULATE (
MAX (monthly_usage_table[Max-Hours] ),
FILTER (
ALL(monthly_usage_table),
monthly_usage_table[serial_number] = EARLIER ( monthly_usage_table[serial_number] )
&& monthly_usage_table[customer_number] = EARLIER ( monthly_usage_table[customer_number] )
&& monthly_usage_table[YearMonth] = EARLIER ( monthly_usage_table[Next_Month] )
)
),if(AND(monthly_usage_table[Max-Hours]< CALCULATE (
MAX (monthly_usage_table[Max-Hours] ),
FILTER (
ALL(monthly_usage_table),
monthly_usage_table[serial_number] = EARLIER ( monthly_usage_table[serial_number] )
&& monthly_usage_table[customer_number] = EARLIER ( monthly_usage_table[customer_number] )
&& monthly_usage_table[YearMonth] = EARLIER ( monthly_usage_table[Next_Month] )
)
),ISBLANK(monthly_usage_table[Max-Hours])=FALSE),CALCULATE (
MAX (monthly_usage_table[Max-Hours] ),
FILTER (
ALL(monthly_usage_table),
monthly_usage_table[serial_number] = EARLIER ( monthly_usage_table[serial_number] )
&& monthly_usage_table[customer_number] = EARLIER ( monthly_usage_table[customer_number] )
&& monthly_usage_table[YearMonth] = EARLIER ( monthly_usage_table[Next_Month] )
)
),monthly_usage_table[Max-Hours]))