Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX/Formula HELP!

Sorry for the vague subject - I am unsure how to categorize this. I am attempting to predict the inventory for the rest of the year. I currently have ending inventory data for months 1 through 5. To ...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous try this measure

     

    Measure 3 = 
SUMX ( 
    FILTER ( ALL ( Bal[MONTH] ), Bal[MONTH] <= MAX ( Bal[MONTH] ) ),
    CALCULATE ( SUM ( Bal[ADDITIONAL] ) ) 
)
+ CALCULATE ( SUM ( Bal[BEG INV] ), Bal[MONTH] = 1 )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous you are pretty much looking for running total

 

Predicted Ending Inventory = 
CALCULATE ( 
SUM ( Table[Beg Inv] ) +
SUM ( Table[Additionaol ),
FILTER ( 
ALL ( Table ),
Table[Month] <= MAX ( Table[Month] )
)

 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

Anonymous's avatar
Anonymous
Not applicable
6 years ago

parry2k thanks for the response.  I think this is in the right direction, however, my results were as follows:

 

MONTHBEG INVADDITIONALEND INVPredicted Ending InventoryPARRY2K FORMULA
1100100200200200
2200100300300500
330050350350850
43502005505501400
5550506006002000
6 100 7002100
7 200 9002300
8 100 10002400
9 100 11002500
10 50 11502550
11 100 12502650
12 50 13002700
  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous ,

     

    Create a new blank query and paste this mcode to create a recursive function:

     

    (_table as table, _month as number, _currentMonth as number, _inventoryValue as number) as number =>
    let
    Source = Table.SelectRows(_table, each [MONTH] = _month),
    BEG_INV = if Source[BEG INV]{0} = null or _month > 1 then 0 else Source[BEG INV]{0},
    ADDITIONAL = if Source[ADDITIONAL]{0} = null then 0 else Source[ADDITIONAL]{0},
    PredictValue = _inventoryValue + ADDITIONAL + BEG_INV,
    Result = if _month < _currentMonth then @fn_PredictValues(_table, _month + 1, _currentMonth, PredictValue) else PredictValue
    in
    Result

     

    Use this mcode to create your base table:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxBCsAgDATAr0jOOSSxse1bxP9/Q7GrSMllWYZNaiUlJhXZaSMbVzL05Rme0Zl8sn98oa8jhzs65gVfyuhp/07Tbpgd9gS7NzAVoB+m0dB+w9YB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MONTH = _t, #"BEG INV" = _t, ADDITIONAL = _t, #"END INV" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MONTH", Int64.Type}, {"BEG INV", Int64.Type}, {"ADDITIONAL", Int64.Type}, {"END INV", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fn_PredictValues(#"Changed Type", 1, [MONTH], 0))
    in
    #"Added Custom"

     

    or create a custom column like this:

     

     

     

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

      Anonymous try this measure

       

      Measure 3 = 
SUMX ( 
    FILTER ( ALL ( Bal[MONTH] ), Bal[MONTH] <= MAX ( Bal[MONTH] ) ),
    CALCULATE ( SUM ( Bal[ADDITIONAL] ) ) 
)
+ CALCULATE ( SUM ( Bal[BEG INV] ), Bal[MONTH] = 1 )

       

      I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

      ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Awesome - thank you so much!