Forum Discussion

HectorMSC's avatar
HectorMSC
Frequent Visitor
2 years ago
Solved

How to apply a dynamic formula?

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!

 

 

 

 

Tips and Tricks
Tutorial Requests
  • parry2k's avatar
    parry2k
    2 years ago

    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

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      HectorMSC
      Frequent Visitor
      2 years ago

      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!