Formula to Calculate Usage

[hamzashafiq]

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 based on Max Hours and Month column. The formula to calculate Usage to get the desired results is listed on Formula column but I don't know how to do this in PowerBI. I am actually taking the difference between Max of (Max Hours column) and Min(Max Hours) column and dividing this with Date difference in Month to get the usage/month. Please see attached screenshot for the data I have and the desired output(Usage).

[v-easonf-msft · Accepted solution]

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.

[hamzashafiq]

@v-easonf-msft Nope same issue, I am also getting invalid value in Start_date column. Please see the below screenshot.

[v-easonf-msft · Accepted solution]

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]

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]))

[hamzashafiq]

@v-easonf-msft can you please help?

[hamzashafiq]

@v-easonf-msft Thank you so much, you made my day. God bless you!

[hamzashafiq]

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]))

[hamzashafiq]

I am getting this error. 

[v-easonf-msft]

Hi, @hamzashafiq 

Try to add an extra set of parameters to the variable Max_Hours in the formula 'Usage', similar to the following ：

VAR Max_hours =
    LOOKUPVALUE (
        'Table'[Diff],
        'Table'[Serial Number], 'Table'[Serial Number],
        'Table'[customer_Number], 'Table'[customer_Number], //***
        'Table'[YearMonth], 'Table'[Start_date]
    )

Best Regards,
Community Support Team _ Eason

[hamzashafiq]

Thanks @v-easonf-msft That's exactly what I needed, thanks again!

[v-easonf-msft · Accepted solution]

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.