DAX Measure For Calculating Stocking Levels

[wiser]

Hi all.

I am trying to create a DAX formula/measure that calculates necessary stocking levels based on several different inputs.  The biggest input is the install base.  Even though it is a very ugly formula in Excel it has worked for a very long time. 

To start I've created two parameters to use in the mesaure, install base and lead time.  Both of these can be with a high range for install base and two choices of for the lead time.  I'm stuck at this point.  I've tried SWITCH TRUE (), and an IF statement.  On both I get stuck on the second SUM category so I don't know if it even works beyond that.

This picture shows the dependents within the Excel formula.  I've attached the link to the Excel file:  https://1drv.ms/x/s!Apq9nVxrTY6rlmm42MbpB5046pP3?e=90IZdy.  Entering any number into the 'segment', cell F4, changes the values below in Tier 1 and 2, columns F and G.  If the link doesn't work please let me know.

The formula that I've tried in DAX is as follows:

IF(
    SUM([Proposed Install Base Value]) * SUM('RIL-Stack 750E - CollectSeg'[Annual Usage]) > '5',
        CALCULATE(
            SUM(
                MAX(
                    ROUNDUP(
                        SUM([Proposed Install Base Value]) * SUM('RIL-Stack 750E - CollectSeg'[Annual Usage]) / (12)),0,
                            SUM('RIL-Stack 750E - CollectSeg'[TTQ]) + SUM('RIL-Stack 750E - CollectSeg'[TTQ])) - (1))
                        +
                            ROUNDUP(
                                SUM(
                                    SUM(
                                        SUM([Proposed Install Base Value]) * SUM('RIL-Stack 750E - CollectSeg'[Annual Usage]) / 365)
                                    *
                                    SUM([Warehouse To Site Leadtime Value]),0)
                                )
                            )
                        ),0
                    )

I appreciate any guidance on this.  Like I said, I'm at a loss.

Thanks.

Will

[Barthel · Accepted solution]

Hey,

I created your table in Excel in Power BI. Above it slicers for the parameters. You can use the following code to calculate 'Tier 1':

Tier 1 = 
VAR _ProposedCS =
    SELECTEDVALUE ( ProposedCS[ProposedCS] )
VAR _PropesedLT =
    SELECTEDVALUE ( ProposedLT[ProposedLT] )
VAR _AnnualUsage =
    SELECTEDVALUE ( 'Table'[Annual Usage] )
VAR _TT =
    SELECTEDVALUE ( 'Table'[TTQ] )
RETURN
    IF (
        _ProposedCS * _AnnualUsage > 5,
        VAR _m1 =
            MAXX (
                {
                    _TT + _TT - 1,
                    ROUNDUP ( DIVIDE ( _ProposedCS * _AnnualUsage, 12 ), 0 )
                },
                [Value]
            )
        VAR _m2 =
            ROUNDUP ( DIVIDE ( _ProposedCS * _AnnualUsage, 365 ) * _PropesedLT, 0 )
        RETURN
            _m1 + _m2,
        0
    )

You can use the above code as a basis for calculating the other calculations. Adjust the parameters and conditions for this. Hope this helps. 

[wiser]

Thank you very much, I really appreciate this!  Hope you have a good weekend.

[daXtreme]

Hi @wiser 

This looks so messy that I seriously doubt anyone would like to try and decipher this... Might be wrong, of course, but one has to have plenty of time on their hands to deal with such an issue 🙂

Can you please somehow simplify it to only single out the most important bits and make the description more granular/comprehensible? Maybe then people will show more enthusiasm about helping. Just a gentle hint 🙂

Cheers.