Calculate Future Date of Restock?

[BugmanJ]

Greetings All,

I have several sweety shops and I am wanting PowerBi to display when, approx based on the rate of sales, the date I need to reorder. (It would be handy to show the average sales over this period)

The data has 3 different sweety shops, one has two jars, one of which is named the same as elseswhere, but thats where it ends (no link to other shop). In addition, one shop has already reordered at some point in the data.

The sales should be calculated  based on the last say 30 days?

I think this measure might be a start, but it falls apart if the jar has been restocked nor how to tie this into a date versus the restock level

M - Prior 30 Days Sales = 
CALCULATE(max('StockLevels'[Level]-min('StockLevels')),
    DATESBETWEEN( 'Calendar'[Date],
    max('Calendar'[Date]) - 30,
    max('Calendar'[Date])-1))/30

Pic of links:

Dummy Data: Here

Thank you in advance

[PaulDBrown · Accepted solution]

See if this works for you. First the model:

The measures:

Level of stock = 
SUM(StockLevels[Level])

Sales and restock =
VAR _Restock =
    CALCULATE (
        MAX ( StockLevels[Date] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] < MAX ( 'Calendar'[Date] )
                && NOT ISBLANK ( [Level of stock] )
        )
    )
VAR _PrevLevel =
    CALCULATE (
        [Level of stock],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _Restock )
    )
RETURN
    _PrevLevel - [Level of stock]

Sales (excl. Restock) = 
SUMX(FILTER('Calendar', [Sales and restock] >= 0), [Sales and restock])

Av daily Sales (30 days) = 
VAR _SelDate = MAX('Calendar'[Date])
VAR _PrevDate = CALCULATE(MAX('Calendar'[Date]), DATEADD('Calendar'[Date], -30, DAY))
VAR _AvTable =  
CALCULATETABLE(
    ADDCOLUMNS(
    SUMMARIZE(StockLevels, 'Shop Table'[Shop], 'Sweety Jar Table'[Sweety Jar], 'Calendar'[Date]), "@Sales", [Sales (excl. Restock)]), 
    DATESBETWEEN('Calendar'[Date], _PrevDate, MAX('Calendar'[Date])))
RETURN
DIVIDE(SUMX(_AvTable, [@Sales]), 30)

Threshold Break =
VAR _MaxDate =
    CALCULATE (
        MAX ( StockLevels[Date] ),
        FILTER (
            ALLEXCEPT ( StockLevels, 'Shop Table'[Shop], 'Sweety Jar Table'[Sweety Jar] ),
            NOT ISBLANK ( [Level of stock] )
        )
    )
VAR _ADS =
    CALCULATE (
        [Av daily Sales (30 days)],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _MaxDate )
    )
VAR _CumulSales =
    SUMX (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] > _MaxDate
                && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        ),
        _ADS
    )
VAR _LastStock =
    IF (
        MAX ( 'Calendar'[Date] ) > _MaxDate,
        CALCULATE (
            [Level of stock],
            FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] = _MaxDate )
        )
    )
VAR _RunningStock = _LastStock - _CumulSales
VAR _ThresholdBreak = _RunningStock - [Reorder Threshold]
RETURN
    IF ( MAX ( 'Calendar'[Date] ) > _MaxDate, _ThresholdBreak )

Re-Order Date =
CALCULATE (
    MAX ( 'Calendar'[Date] ),
    FILTER (
        ALL ( 'Calendar' ),
        NOT ISBLANK ( [Threshold Break] )
            && [Threshold Break] >= 0
    )
)

Last Av Daily Sale =
VAR _MaxDate =
    CALCULATE (
        MAX ( StockLevels[Date] ),
        FILTER (
            ALLEXCEPT ( StockLevels, 'Shop Table'[Shop], 'Sweety Jar Table'[Sweety Jar] ),
            NOT ISBLANK ( [Level of stock] )
        )
    )
RETURN
    CALCULATE (
        [Av daily Sales (30 days)],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _MaxDate )
    )

Stock at Re-Order Date =
CALCULATE (
    [Threshold Break],
    FILTER ( 'Calendar', 'Calendar'[Date] = [Re-Order Date] )
) + [Reorder Threshold]

To get

Sample PBIX file attached

[BugmanJ]

@PaulDBrown Sorry for not getting back to you sooner, this works so well in my larger dataset, thank you so much!!

[BugmanJ]

@PaulDBrown  this is just amazing, thank you so much, it works perfectly for my needs

[BugmanJ]

@PaulDBrown  Wow thats amazing!!! Thank you for putting the time and effort into that!. Quick question though, is there a reason its not projecting a date for South Street 1A sweety jar? This is the jar that has been restocked during the data pull.

Many thanks!

[PaulDBrown · Accepted solution]

@BugmanJ 

The reason no date is returned for South Street 1A is that the Threshold Break measure was calculating from the date following the last stock level value, and the date was checking for the last date where the Threshold Break measure returns a positive number. It so happens that for South Street 1A, the threshold break measure was already returning a negative number as the first value (so no positive found). 
I

I've thought of a simpler way of obtaining the measures you need:

Av daily Sales (30 days) =
VAR _SelDate =
    MAX ( 'Calendar'[Date] )
VAR _PrevDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), DATEADD ( 'Calendar'[Date], -30, DAY ) )
VAR _AvTable =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER ( StockLevels, NOT ISBLANK ( [Level of stock] ) ),
                'Shop Table'[Shop],
                'Sweety Jar Table'[Sweety Jar],
                'Calendar'[Date]
            ),
            "@Sales", [Sales (excl. Restock)]
        ),
        DATESBETWEEN ( 'Calendar'[Date], _PrevDate, MAX ( 'Calendar'[Date] ) )
    )
RETURN
    DIVIDE ( SUMX ( _AvTable, [@Sales] ), 30 )

Re-Order Date =
VAR _MaxDate =
    CALCULATE (
        MAX ( StockLevels[Date] ),
        FILTER (
            ALLEXCEPT ( StockLevels, 'Shop Table'[Shop], 'Sweety Jar Table'[Sweety Jar] ),
            NOT ISBLANK ( [Level of stock] )
        )
    )
VAR _ADS =
    CALCULATE (
        [Av daily Sales (30 days)],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _MaxDate )
    )
VAR _LastStock =
    CALCULATE (
        [Level of stock],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _MaxDate )
    )
VAR _Margin =
    DIVIDE ( _LastStock - [Reorder Threshold], _ADS )
VAR _date =
    _MaxDate + ROUNDDOWN ( _Margin, 0 )
RETURN
    IF (
        ISBLANK ( [Last Av Daily Sale] ),
        BLANK (),
        IF ( _date - 1 < _MaxDate, _MaxDate, _date - 1 )
    )

Stock at Re-Order Date =
VAR _MaxDate =
    CALCULATE (
        MAX ( StockLevels[Date] ),
        FILTER (
            ALLEXCEPT ( StockLevels, 'Shop Table'[Shop], 'Sweety Jar Table'[Sweety Jar] ),
            NOT ISBLANK ( [Level of stock] )
        )
    )
VAR _ADS =
    CALCULATE (
        [Av daily Sales (30 days)],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _MaxDate )
    )
VAR _LastStock =
    CALCULATE (
        [Level of stock],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _MaxDate )
    )
VAR _Margin =
    DIVIDE ( _LastStock - [Reorder Threshold], _ADS )
VAR _Sales =
    _ADS * ROUNDDOWN ( _Margin, 0 )
VAR _SORD = _LastStock - _Sales
RETURN
    IF ( _MaxDate = [Re-Order Date], _LastStock - _ADS, _SORD )

Last Av Daily Sale =
VAR _MaxDate =
    CALCULATE (
        MAX ( StockLevels[Date] ),
        FILTER (
            ALLEXCEPT ( StockLevels, 'Shop Table'[Shop], 'Sweety Jar Table'[Sweety Jar] ),
            NOT ISBLANK ( [Level of stock] )
        )
    )
RETURN
    CALCULATE (
        [Av daily Sales (30 days)],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _MaxDate )
    )

Out of stock margin (days) = 
DIVIDE([Stock at Re-Order Date], [Last Av Daily Sale])

Out of stock date = [Re-Order Date] + ROUND([Out of stock margin (days)], 0)

Sample PBIX file attached

[PaulDBrown · Accepted solution]

See if this works for you. First the model:

The measures:

Level of stock = 
SUM(StockLevels[Level])

Sales and restock =
VAR _Restock =
    CALCULATE (
        MAX ( StockLevels[Date] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] < MAX ( 'Calendar'[Date] )
                && NOT ISBLANK ( [Level of stock] )
        )
    )
VAR _PrevLevel =
    CALCULATE (
        [Level of stock],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _Restock )
    )
RETURN
    _PrevLevel - [Level of stock]

Sales (excl. Restock) = 
SUMX(FILTER('Calendar', [Sales and restock] >= 0), [Sales and restock])

Av daily Sales (30 days) = 
VAR _SelDate = MAX('Calendar'[Date])
VAR _PrevDate = CALCULATE(MAX('Calendar'[Date]), DATEADD('Calendar'[Date], -30, DAY))
VAR _AvTable =  
CALCULATETABLE(
    ADDCOLUMNS(
    SUMMARIZE(StockLevels, 'Shop Table'[Shop], 'Sweety Jar Table'[Sweety Jar], 'Calendar'[Date]), "@Sales", [Sales (excl. Restock)]), 
    DATESBETWEEN('Calendar'[Date], _PrevDate, MAX('Calendar'[Date])))
RETURN
DIVIDE(SUMX(_AvTable, [@Sales]), 30)

Threshold Break =
VAR _MaxDate =
    CALCULATE (
        MAX ( StockLevels[Date] ),
        FILTER (
            ALLEXCEPT ( StockLevels, 'Shop Table'[Shop], 'Sweety Jar Table'[Sweety Jar] ),
            NOT ISBLANK ( [Level of stock] )
        )
    )
VAR _ADS =
    CALCULATE (
        [Av daily Sales (30 days)],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _MaxDate )
    )
VAR _CumulSales =
    SUMX (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] > _MaxDate
                && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        ),
        _ADS
    )
VAR _LastStock =
    IF (
        MAX ( 'Calendar'[Date] ) > _MaxDate,
        CALCULATE (
            [Level of stock],
            FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] = _MaxDate )
        )
    )
VAR _RunningStock = _LastStock - _CumulSales
VAR _ThresholdBreak = _RunningStock - [Reorder Threshold]
RETURN
    IF ( MAX ( 'Calendar'[Date] ) > _MaxDate, _ThresholdBreak )

Re-Order Date =
CALCULATE (
    MAX ( 'Calendar'[Date] ),
    FILTER (
        ALL ( 'Calendar' ),
        NOT ISBLANK ( [Threshold Break] )
            && [Threshold Break] >= 0
    )
)

Last Av Daily Sale =
VAR _MaxDate =
    CALCULATE (
        MAX ( StockLevels[Date] ),
        FILTER (
            ALLEXCEPT ( StockLevels, 'Shop Table'[Shop], 'Sweety Jar Table'[Sweety Jar] ),
            NOT ISBLANK ( [Level of stock] )
        )
    )
RETURN
    CALCULATE (
        [Av daily Sales (30 days)],
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _MaxDate )
    )

Stock at Re-Order Date =
CALCULATE (
    [Threshold Break],
    FILTER ( 'Calendar', 'Calendar'[Date] = [Re-Order Date] )
) + [Reorder Threshold]

To get

Sample PBIX file attached