Need to Carry Value to Next Month if Demand Exceeds Capacity

[Anonymous]

Hello everyone. I have been struggling with this report. I have a demand and capacity report and I need it to carry the value to the next month when the demand exceeds capacity. If it exceeds the following month, it needs to be carried over until it doesn't. I have been unable to come up with a solution in both dax and power query. Does anyone know how to solve this? Thank you in advance.

[Anonymous · Accepted solution]

Hello everyone. I was actually able to solve this problem myself. For anyone with the same issue, feel free to use my calculations. You will have to repeat for each month, in my case for 12 months. Hope this helps. Feel free to comment if you have a more optimized solution.

Calculation 1:

Prev Month Overflow = CALCULATE(

        SUM('Capacity Table'[Capacity Hours])- SUM('Demand Table'[Demand Hours]),

        PREVIOUSMONTH(dim_date[Date]))*SIGN(SUM('Demand Table'[Demand Hours]) - SUM('Capacity Table'[Capacity Hours]))

Calculation 2:

Demand Hours Overflow =

IF(ISBLANK(SUM('Capacity Table'[Capacity Hours]))=True,SUM('Demand Table'[Demand Hours]),

IF(SIGN(SUM('Demand Table'[Demand Hours]) - SUM('Capacity Table'[Capacity Hours]))=1,SUM('Capacity Table'[Capacity Hours]),

IF([Prev Month Overflow]>0, [Prev Month Overflow]+SUM('Demand Table'[Demand Hours]),

SUM('Demand Table'[Demand Hours]))))

After repeating these calculations it should look like this for future calculations: 2-12:

Calculation 1:

Prev Month Overflow 2 = CALCULATE(

        SUM('Capacity Table'[Capacity Hours])- [Demand Hours Overflow],

        PREVIOUSMONTH(dim_date[Date]))*SIGN([Demand Hours Overflow] - SUM('Capacity Table'[Capacity Hours]))

Calculation 2:

Demand Hours Overflow 2 =

IF(ISBLANK(SUM('Capacity Table'[Capacity Hours]))=True,[Demand Hours Overflow],

IF(SIGN([Demand Hours Overflow] - SUM('Capacity Table'[Capacity Hours]))=1,SUM('Capacity Table'[Capacity Hours]),

IF([Prev Month Overflow 2]>0, [Prev Month Overflow 2]+[Demand Hours Overflow],

[Demand Hours Overflow])))

First couple of columns are shown above.

[Anonymous]

Also, forgot to mention that the demand and capacity are seperate tables. The demand is a long query but can be summarized if needed. The capacity is a 12 row table for each month of the year.