Forum Discussion

LauraWoods's avatar
LauraWoods
Frequent Visitor
2 years ago
Solved

Inventory Months on Hand replacing if statement with Switch

Hi 

 

I am trying to replace this long if statment with a switch can anyone help?

 

MOH Forecast Count forward =

Var SemenInventory =  [**bleep** Inventory Future Balance]
var PredictedsalesMOH = [Predicted sales MOH]
Var Rev = PredictedsalesMOH
Var Rev1 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 1, Month))
Var Rev2 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 2, Month))
Var Rev3 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 3, Month))
Var Rev4 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 4, Month))
Var Rev5 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 5, Month))
Var Rev6 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 6, Month))
Var Rev7 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 7, Month))
Var Rev8 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 8, Month))
Var Rev9 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 9, Month))
Var Rev10 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 10, Month))
Var Rev11 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 11, Month))
Var Rev12 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 12, Month))
Var Rev13 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 13, Month))
Var Rev14 = calculate(PredictedsalesMOH, DATEADD('130 Date'[DateKey] , 14, Month))
Return

IF(SemenInventory<Rev1,ROUNDUP(IFERROR(SemenInventory/IFERROR(Rev1/30,BLANK()),0),0)+30,
IF(SemenInventory<(Rev1 + Rev2), ROUNDUP(IFERROR((SemenInventory-Rev1)/IFERROR(Rev2/30,0),0),0)+60,
if(SemenInventory<(Rev1 + Rev2 + Rev3), ROUNDUP(IFERROR((SemenInventory-(Rev1+Rev2))/IFERROR(Rev3/30,0),0),0)+90,
if(SemenInventory<(Rev1 + Rev2 + Rev3 + Rev4), ROUNDUP(IFERROR((SemenInventory-(Rev1+Rev2+Rev3))/IFERROR(Rev4/30,0),0),0)+120,
if(SemenInventory<(Rev1 + Rev2 + Rev3 + Rev4 + Rev5), ROUNDUP(IFERROR((SemenInventory-(Rev1+Rev2+Rev3+rev4))/IFERROR(Rev5/30,0),0),0)+150,
if(SemenInventory<(Rev1 + Rev2 + Rev3 + Rev4 + Rev5 + Rev6), ROUNDUP(IFERROR((SemenInventory-(Rev1+Rev2+Rev3+Rev4+rev5))/IFERROR(Rev6/30,0),0),0)+180,
if(SemenInventory<(Rev1 + Rev2 + Rev3 + Rev4 + Rev5 + Rev6 + Rev7), ROUNDUP(IFERROR((SemenInventory-(Rev1+Rev2+Rev3+Rev4+Rev5+rev6))/IFERROR(Rev7/30,0),0),0)+210,
if(SemenInventory<(Rev1 + Rev2 + Rev3 + Rev4 + Rev5 + Rev6 + Rev7 + Rev8), ROUNDUP(IFERROR((SemenInventory-(Rev1+Rev2+Rev3+Rev4+Rev5+Rev6+rev7))/IFERROR(Rev8/30,0),0),0)+240,
if(SemenInventory<(Rev1 + Rev2 + Rev3 + Rev4 + Rev5 + Rev6 + Rev7 + Rev8 + Rev9), ROUNDUP(IFERROR((SemenInventory-(Rev1+Rev2+Rev3+Rev4+Rev5+Rev6+Rev7+rev8))/IFERROR(Rev9/30,0),0),0)+270,
if(SemenInventory<(Rev1 + Rev2 + Rev3 + Rev4 + Rev5 + Rev6 + Rev7 + Rev8 + Rev9 + Rev10), ROUNDUP(IFERROR((SemenInventory-(Rev1+Rev2+Rev3+Rev4+Rev5+Rev6+Rev7+Rev8+rev9))/IFERROR(Rev10/30,0),0),0)+300,
if(SemenInventory<(Rev1 + Rev2 + Rev3 + Rev4 + Rev5 + Rev6 + Rev7 + Rev8 + Rev9 + Rev10+Rev11), ROUNDUP(IFERROR((SemenInventory-(Rev1+Rev2+Rev3+Rev4+Rev5+Rev6+Rev7+Rev8+rev9+Rev10))/IFERROR(Rev11/30,0),0),0)+330,
if(SemenInventory<(Rev1 + Rev2 + Rev3 + Rev4 + Rev5 + Rev6 + Rev7 + Rev8 + Rev9 + Rev10+Rev11+Rev12), ROUNDUP(IFERROR((SemenInventory-(Rev1+Rev2+Rev3+Rev4+Rev5+Rev6+Rev7+Rev8+rev9+Rev10+rev11))/IFERROR(Rev12/30,0),0),0)+360,
if(SemenInventory<(Rev1 + Rev2 + Rev3 + Rev4 + Rev5 + Rev6 + Rev7 + Rev8 + Rev9 + Rev10+Rev11+Rev12+Rev13), ROUNDUP(IFERROR((SemenInventory-(Rev1+Rev2+Rev3+Rev4+Rev5+Rev6+Rev7+Rev8+rev9+Rev10+rev11+Rev12))/IFERROR(Rev13/30,0),0),0)+390,
if(SemenInventory<(Rev1 + Rev2 + Rev3 + Rev4 + Rev5 + Rev6 + Rev7 + Rev8 + Rev9 + Rev10+Rev11+Rev12+Rev13+rev14), ROUNDUP(IFERROR((SemenInventory-(Rev1+Rev2+Rev3+Rev4+Rev5+Rev6+Rev7+Rev8+rev9+Rev10+rev11+Rev12+rev13))/IFERROR(Rev14/30,0),0),0)+420,450)))))))))))))/30


)
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi LauraWoods ,

    Please try below dax formula:

    MOH Forecast Count forward =
VAR SemenInventory = [**bleep** Inventory Future Balance]
VAR PredictedsalesMOH = [Predicted sales MOH]
VAR Rev =
    {
        PredictedsalesMOH,
        CALCULATE ( PredictedsalesMOH, DATEADD ( '130 Date'[DateKey], 1, MONTH ) ),
        CALCULATE ( PredictedsalesMOH, DATEADD ( '130 Date'[DateKey], 2, MONTH ) ),
        ...
        CALCULATE ( PredictedsalesMOH, DATEADD ( '130 Date'[DateKey], 14, MONTH ) )
    }
VAR CumulativeRev =
    ADDCOLUMNS (
        GENERATESERIES ( 1, 14, 1 ),
        "CumRev", CALCULATE ( SUMX ( TOPN ( [Value], Rev ), [Value] ) )
    )
VAR Result =
    SWITCH (
        TRUE (),
        SemenInventory < LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 1 ),
            ROUNDUP (
                SemenInventory
                    / ( LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 1 ) / 30 ),
                0
            ) + 30,
        SemenInventory < LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 2 ),
            ROUNDUP (
                (
                    SemenInventory - LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 1 )
                )
                    / ( LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 2 ) / 30 ),
                0
            ) + 60,
        ...
        SemenInventory < LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 14 ),
            ROUNDUP (
                (
                    SemenInventory - LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 13 )
                )
                    / ( LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 14 ) / 30 ),
                0
            ) + 420,
        450
    )
RETURN
    Result / 30

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Have you tested this? I don't think it's going to work because at the start the variables are storing the evaluated measure and then the variable is used in different calculate statements but once the measure is evaluated, it is a fixed amount so they are all going to return the same amount.

    Do you want to show some sample data and state what you are trying to do? There's bound to be a better way.

    --

    And please, to spare your blushes, change the name of that variable.  It's very funny but even the powerbi forum website beeped it out!

    • LauraWoods's avatar
      LauraWoods
      Frequent Visitor
      2 years ago

      Hi Apologies I have not tested this out yet. I will share some sample data for what i would like the outcome to be. I didnt even think of the name of the variable it is the business we work in apologies it bioengineering and this is the product we have.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi LauraWoods ,

    Please try below dax formula:

    MOH Forecast Count forward =
VAR SemenInventory = [**bleep** Inventory Future Balance]
VAR PredictedsalesMOH = [Predicted sales MOH]
VAR Rev =
    {
        PredictedsalesMOH,
        CALCULATE ( PredictedsalesMOH, DATEADD ( '130 Date'[DateKey], 1, MONTH ) ),
        CALCULATE ( PredictedsalesMOH, DATEADD ( '130 Date'[DateKey], 2, MONTH ) ),
        ...
        CALCULATE ( PredictedsalesMOH, DATEADD ( '130 Date'[DateKey], 14, MONTH ) )
    }
VAR CumulativeRev =
    ADDCOLUMNS (
        GENERATESERIES ( 1, 14, 1 ),
        "CumRev", CALCULATE ( SUMX ( TOPN ( [Value], Rev ), [Value] ) )
    )
VAR Result =
    SWITCH (
        TRUE (),
        SemenInventory < LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 1 ),
            ROUNDUP (
                SemenInventory
                    / ( LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 1 ) / 30 ),
                0
            ) + 30,
        SemenInventory < LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 2 ),
            ROUNDUP (
                (
                    SemenInventory - LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 1 )
                )
                    / ( LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 2 ) / 30 ),
                0
            ) + 60,
        ...
        SemenInventory < LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 14 ),
            ROUNDUP (
                (
                    SemenInventory - LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 13 )
                )
                    / ( LOOKUPVALUE ( CumulativeRev[CumRev], CumulativeRev[Value], 14 ) / 30 ),
                0
            ) + 420,
        450
    )
RETURN
    Result / 30

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.