How to apply a dynamic formula?

[HectorMSC]

Hello! I hope everyone is doing well. Today, I'd like some assistance about a crucial aspect of inventory management: the BOM (Beginning of Month) formula. The BOM formula plays a vital role in helping businesses effectively manage their inventory and avoid overstocks.

BOM (Beginning of Month) Formula: BOM = MonthPlan + (AnnualPlan) * ((1/Rot) - (1/12))

• MonthPlan: This represents the monthly sales plan. It reflects the expected sales for a particular month.

• AnnualPlan: The AnnualPlan refers to the annual sales plan.

• Rot (Rate of Turnover): Rot is a crucial parameter that indicates how quickly inventory turns over during the year. 
  
  

  
  
  For example, January should be:
  351,710.26 + (5,140,233.08)((1/6)-(1/12)) = 780,063.
  
  Febreaury should be:
  355283.55 + (5,140,233.08)((1/6)-(1/12)) = 783,636 and so on.
  
  Right now my formula is: 

BOM Plan = 
VAR FirstMonthPlan = CALCULATE(
    SUMX('Dashboard', [Plan]),
    FILTER('Dashboard', 'Dashboard'[Month] = 01)
)

VAR AnnualSalesPlan = CALCULATE(
    SUMX('Dashboard', [Plan]),
    ALLEXCEPT('Dashboard', 'Dashboard'[Year])
)

RETURN
SUMX(
    'Dashboard',
    VAR Rot = [Rot]
    RETURN
    FirstMonthPlan + (AnnualSalesPlan * (1 / (Rot - 1/12)))
)

And I'm getting this:

Thanks in advance for any assistance!

[parry2k]

@HectorMSC glad you have the solution. Cheers!!

[parry2k · Accepted solution]

@HectorMSC 

BOM Plan Measure = 
VAR __MonthPlan = [Plan] --assuming this is a measure which is sum ( Table[Plan] )
VAR __AnnualPlan = CALCULATE ( [Plan], ALLSELECTED ( Table[Months] ) ) --month column shown on the column in the matrix visual
VAR __Rot = [ROT] --assuming it is a measure
VAR __Result = __MonthPlan + ( __AnnualPlan * ( 1 / ( __Rot - 1 / 12 ) ) )
RETURN
    __Result

[HectorMSC]

Hello @parry2k !
Thanks for the code, I just used the code you provide but I had a problem with the AnnualPlan, because it was selecting the monthly plan, not the sum of every month.

However, I create a column called "Total Plan Year"

Total Plan Year = 
SUMX(
    FILTER('Dashboard', 'Dashboard'[Year] = 2022),
    'Dashboard'[Ventas Total] * 1.3
)

And I used your code:

BOM Plan Measure = 
VAR __MonthPlan = [Plan] --assuming this is a measure which is sum ( Table[Plan] )
VAR __AnnualPlan =
AVERAGE(Dashboard[Total Plan Year])
VAR __Rot = [RotM] --assuming it is a measure
VAR __Result = __MonthPlan + ( __AnnualPlan * (( 1 / __Rot) - 1 / 12) )
RETURN
    __Result

Now I have what I was looking for, thank you!