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.⚡

caochs's avatar
caochs
Regular Visitor
6 years ago

Hi Anonymous - I see that you already marked the solution, but thought I'd share this measure with you any way.  Let me know if you have any questions:

 

 

 

Predicted Ending Inv = 
//Find the last month with an ending inventory for use later
VAR maxEndInvMonth =
    CALCULATE (
        MIN ( 'Table'[MONTH] ),
        FILTER ( ALL ( 'Table' ), ISBLANK ( 'Table'[END INV] ) )
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE (
                'Table',
                'Table'[MONTH],
                'Table'[END INV],
                //Start the running total once there is no longer an ending inventory
                "RunningAdditionalNoEndingInventory", CALCULATE (
                    SUM ( 'Table'[ADDITIONAL] ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[MONTH] <= MAX ( 'Table'[MONTH] )
                            && 'Table'[MONTH] >= maxEndInvMonth
                    )
                )
            ),
            //This column is for demonstration purposes only and is not needed for the calculation
            "EarlierEndInv", CALCULATE (
                MAX ( 'Table'[END INV] ),
                FILTER ( 'table', 'table'[MONTH] < EARLIER ( 'table'[MONTH], 1 ) )
            ),
            //Check if the month has an ending inventory, if it does not then use the running total plus the "EARLIER" ending inventory.  If it has an ending inventory use that instead.
            "PredictedEndingInv", IF (
                CALCULATE ( MAX ( 'Table'[MONTH] ) ) >= maxEndInvMonth,
                CALCULATE (
                    MAX ( 'Table'[END INV] ),
                    FILTER ( ALL ( 'table' ), 'table'[MONTH] < EARLIER ( 'table'[MONTH], 1 ) )
                ) + [RunningAdditionalNoEndingInventory],
                'Table'[END INV]
            )
        ),
        [PredictedEndingInv]
    )

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

caochs thank you!!! Very helpful.