Forum Discussion

hamzashafiq's avatar
hamzashafiq
Kudo 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 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).

 

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

10 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    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
      Community Support
      4 years ago

      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

    • v-easonf-msft's avatar
      v-easonf-msft
      Community 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