Forum Discussion

Kalvin11's avatar
Kalvin11
Regular Visitor
3 years ago

Calculating Burn rate monthly compounding

Hello, I would need some help in a formula to put in PowerBI to calculate the burn rate, but for it to be compounding over a monthly period.

 

I have a couple of values that are being used. Monthly Actuals, Month, and Monthly Burn Rate. 

 

Monthly Actuals = Amount of FTE burned in a month

Months = months throughout the year

Monthly Burn rate = Monthly Actuals/12 (helps get the monthly FTE burn rate)

 

 

I would like this burn rate to be coumpouded from the previous months burn rate. For example if January had monthly actuals of 140FTE, this / 12 = 12. Then February has a monthly actual of 160FTE, dividing this by 12 would equal 13.33, the burn rate would then be (12+13.33) = 25.33. 

dax formula

1 Reply

  • ichavarria's avatar
    ichavarria
    Icon for Solution Specialist rankSolution Specialist
    3 years ago

    Hi Kalvin11,

     

    To calculate the compounded burn rate for each month, you can use the following formula in PowerBI:

     

    Cumulative Burn Rate =
    VAR MonthlyActuals = [Monthly Actuals]
    VAR BurnRate = MonthlyActuals/12
    RETURN
    IF (
    ISBLANK ( MonthlyActuals ),
    BLANK (),
    CALCULATE (
    SUMX (
    FILTER (
    ADDCOLUMNS (
    GENERATESERIES ( 1, MONTH ( TODAY () ) ),
    "MonthNum", [Value]
    ),
    [MonthNum] <= MONTH ( [Month] )
    ),
    [BurnRate]
    ),
    FILTER (
    ALLSELECTED ( Table1 ),
    [Month] <= MAX ( Table1[Month] )
    )
    )
    )

     

    This formula uses a combination of variables, filters, and functions to calculate the cumulative burn rate for each month. Here's a breakdown of how it works:

     

    • First, the formula creates two variables: MonthlyActuals and BurnRate. MonthlyActuals represents the amount of FTE burned in a month, and BurnRate represents the monthly FTE burn rate.
    • Next, the formula checks if MonthlyActuals is blank. If it is, the formula returns blank. If it isn't, the formula proceeds to calculate the cumulative burn rate.
    • To calculate the cumulative burn rate, the formula uses the FILTER and ADDCOLUMNS functions to create a table that lists all the months in the year up to and including the current month. It then uses the SUMX function to sum the monthly burn rates for each of these months.
    • Finally, the formula uses the FILTER function to ensure that the calculation is only performed for months up to and including the current month.

     

    Note that this formula assumes that your data is stored in a table called "Table1" and that your columns are named "Monthly Actuals" and "Month". If your column names are different, you will need to adjust the formula accordingly.

     

    Best regards, 

    Isaac Chavarria

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