Days Since Last Sale and Data Accumulation Since Last Sale

[Anonymous]

Hi Forum,

I am trying to create additional columns for a retail data set which tell me how long since there was a sale for a particular store / item combination and what is the lost sales (a pre-calculated measure) since that event.

I have a data set all in one table containing:
Date
Item
Store
Sales Volume
Lost Sales Volume (a pre calculated data point)

For each Date / Item / Store I am looking to create two additional columns:
Days Since Last Sales
Sum of Lost Sales Volume Since Last Sale

So the input and output are like this:

I've saved a PBI file with two stores / two products / two months here:
EDIT: Updated link on One Drive: https://1drv.ms/u/s!Ahf0U4RCUgvygt10aBKkCfybt4uKvg?e=dlarx0 

Note that the full file is 200 stores x 14,000 products so there's a risk this could be very slow!

I'm 'very stuck' on this - any help would be massively appreciated.

[tamerj1]

Hi @Anonymous 

This requires filter of many conditions so you need check the performance on your file. I wrote the code using my phone on DAX Formatter and copy paste in the reply so please forgive the bad format. 
please use the following codes

Days Since Last Sale =
VAR CurrentDate = Table[Date]
VAR CurrentStore = Table[StoreID]
VAR CurrentProduct = Table[ProductDate]
VAR FilteredTable =
FILTER (
Table,
Table[Date] <= CurrentDate
&& Table[StoreID] = CurrentStore
&& Table[ProductDate]
&& Table[Lost Sales Volum] > 0
)
VAR Result =
COUNTROWS ( FilteredTable )
RETURN
Result

 Lost Sales Volume Since Last Sale =
VAR CurrentDate = Table[Date]
VAR CurrentStore = Table[StoreID]
VAR CurrentProduct = Table[ProductDate]
VAR FilteredTable =
FILTER (
Table,
Table[Date] <= CurrentDate
&& Table[StoreID] = CurrentStore
&& Table[ProductDate]
&& Table[Lost Sales Volum] > 0
)
VAR Result =
SUMX ( FilteredTable, Table[Lost Sales Volum] )
RETURN
Result

 I hope I didn't make any mistake. Please use your own table name in the code. Let me know if it works and if does please don't forget to mark this reply as accepted solution. Thank you snd have a great day

[Anonymous]

Hey  

Thanks so much for this. I've recreated and it's close, but not quite working. The output I am seeing seems to be accumulating since the first instance of no sales and then not 're-setting' after the item sold again.

Could the issue be that I am using the Date from the AFS_Date rather than the independent Date table?

I've re-saved the sample file on the same One Drive link as previously with this code.

This is the output I am seeing, filtered for one Item and Store: 

And this is the code after I adjusted the table and measure names:

Days Since Last Sale = 
VAR CurrentDate = AFS_Data[Date]
VAR CurrentStore = AFS_Data[StoreID]
VAR CurrentProduct = AFS_Data[ProductID]
VAR FilteredTable =
    FILTER (
        AFS_Data,
        AFS_Data[Date] <= CurrentDate
            && AFS_Data[StoreID] = CurrentStore
            && AFS_Data[ProductID] = CurrentProduct
            && AFS_Data[Vol_Lost_Adj] > 0
)
VAR Result =
    COUNTROWS ( FilteredTable )
RETURN
    Result

Lost Sales Volume Since Last Sale = 
VAR CurrentDate = AFS_Data[Date]
VAR CurrentStore = AFS_Data[StoreID]
VAR CurrentProduct = AFS_Data[ProductID]
VAR FilteredTable =
    FILTER (
        AFS_Data,
        AFS_Data[Date] <= CurrentDate
            && AFS_Data[StoreID] = CurrentStore
            && AFS_Data[ProductID] = CurrentProduct
            && AFS_Data[Vol_Lost_Adj] > 0
    ) 
VAR Result =
    SUMX ( FilteredTable, AFS_Data[Vol_Lost_Adj] )
RETURN
    Result

[tamerj1]

Yes that is exactly what you asked for. The resetting will be per store also. Otherwise, it shall be ven easier. Just need to take it out of the filter conditions. Please confirm and let me know

[tamerj1]

[tamerj1]

Sorry @Anonymous I got little confused.

Yes you are right. Here is the updated file https://www.dropbox.com/t/5ppKfOjzqu34C9ck

Use this updated Code

Days Since Last Sale = 
VAR CurrentDate = AFS_Data[Date]
VAR CurrentStore = AFS_Data[StoreID]
VAR CurrentProduct = AFS_Data[ProductID]
VAR FilteredTable =
    FILTER (
        AFS_Data,
        AFS_Data[Date] <= CurrentDate
            && AFS_Data[StoreID] = CurrentStore
                && AFS_Data[ProductID] = CurrentProduct
    )
VAR Total = COUNTROWS ( FilteredTable )
VAR Result = IF ( AFS_Data[Vol_Lost_Adj] > 0, Total )
RETURN
    Result

and

Lost Sales Volume Since Last Sale = 
VAR CurrentDate = AFS_Data[Date]
VAR CurrentStore = AFS_Data[StoreID]
VAR CurrentProduct = AFS_Data[ProductID]
VAR FilteredTable =
    FILTER (
        AFS_Data,
        AFS_Data[Date] <= CurrentDate
            && AFS_Data[StoreID] = CurrentStore
                && AFS_Data[ProductID] = CurrentProduct
    )
VAR Total = SUMX ( FilteredTable, AFS_Data[Vol_Lost_Adj] )
VAR Result = IF ( AFS_Data[Vol_Lost_Adj] > 0, Total )
RETURN
    Result

Hope everything is ok now. Please confirm

[Anonymous]

@tamerj1 

Really appreciate your help (and patience) on this. It's closer but still not quite right.

"Days Since LAST Sale" is currently returning "Date Since FIRST Sale"
"Lost Sales SInce LAST Sale" is currently returning "Lost Sales Accumulated Total"

Image below to explain

[tamerj1]

Hi @Anonymous 
Now I got a better understanding of your requirement. I changed to measures in order to take advantage of the filter context. Now the question is do you want to start counting days from the last date with sales (as per the below screenshot) or just to start from 1 for every no-sale cycle?

[tamerj1 · Accepted solution]

@Anonymous 
Here is your file updated as per your requirement https://www.dropbox.com/t/Tu3niLbsITR7X9Hh
The report looks like this

Not sure about the day count requirement so I prepared both options. Measures are:

Days Since Last Sale1 = 
VAR CurrentDate =
    MAX ( AFS_Data[Date] )
VAR CurrentStore =
    VALUES ( AFS_Data[StoreID] )
VAR CurrentProduct =
    VALUES ( AFS_Data[ProductID] )
VAR LastSalesDate =
    CALCULATE (
        MAX ( AFS_Data[Date] ),
        REMOVEFILTERS ( 'Date'[Date] ),
        AFS_Data[Date] < CurrentDate,
        ISBLANK ( AFS_Data[Vol_Lost_Adj] )
    )
VAR CurrentNoSalesTable =
    CALCULATETABLE (
        AFS_Data,
        REMOVEFILTERS ( 'Date'[Date] ),
        AFS_Data[Date] <= CurrentDate,
        AFS_Data[Date] > LastSalesDate
    )
VAR Result =
    IF (
        NOT ISBLANK (
            FIRSTNONBLANKVALUE ( AFS_Data[Vol_Lost_Adj], VALUES ( AFS_Data[Vol_Lost_Adj] ) )
        ),
        COUNTROWS ( CurrentNoSalesTable )
    )
RETURN
    Result

Days Since Last Sale = 
VAR CurrentDate =
    MAX ( AFS_Data[Date] )
VAR CurrentStore =
    VALUES ( AFS_Data[StoreID] )
VAR CurrentProduct =
    VALUES ( AFS_Data[ProductID] )
VAR LastSalesDate =
    CALCULATE (
        MAX ( AFS_Data[Date] ),
        REMOVEFILTERS ( 'Date'[Date] ),
        AFS_Data[Date] < CurrentDate,
        NOT ISBLANK ( AFS_Data[Vol_Fact] )
    )
VAR CurrentNoSalesTable =
    CALCULATETABLE (
        AFS_Data,
        REMOVEFILTERS ( 'Date'[Date] ),
        AFS_Data[Date] <= CurrentDate,
        AFS_Data[Date] > LastSalesDate
    )
VAR Result =
    IF (
        NOT ISBLANK (
            FIRSTNONBLANKVALUE ( AFS_Data[Vol_Lost_Adj], VALUES ( AFS_Data[Vol_Lost_Adj] ) )
        ),
        COUNTROWS ( CurrentNoSalesTable )
    )
RETURN
    Result

Lost Sales Volume Since Last Sale = 
VAR CurrentDate =
    MAX ( AFS_Data[Date] )
VAR CurrentStore =
    VALUES ( AFS_Data[StoreID] )
VAR CurrentProduct =
    VALUES ( AFS_Data[ProductID] )
VAR LastSalesDate =
    CALCULATE (
        MAX ( AFS_Data[Date] ),
        REMOVEFILTERS ( 'Date'[Date] ),
        AFS_Data[Date] < CurrentDate,
        NOT ISBLANK ( AFS_Data[Vol_Fact] )
    )
VAR CurrentNoSalesTable =
    CALCULATETABLE (
        AFS_Data,
        REMOVEFILTERS ( 'Date'[Date] ),
        AFS_Data[Date] <= CurrentDate,
        AFS_Data[Date] > LastSalesDate
    )
VAR LostSalesSinceLastSaleRT =
    SUMX ( 
        CurrentNoSalesTable,
        AFS_Data[Vol_Lost_Adj]
    )
VAR Result =
    IF (
        NOT ISBLANK (
            FIRSTNONBLANKVALUE ( AFS_Data[Vol_Lost_Adj], VALUES ( AFS_Data[Vol_Lost_Adj] ) )
        ),
        LostSalesSinceLastSaleRT
    )
RETURN
    Result

Please let me know if this solved your problem

[Anonymous]

Wonderful - that's the baby.
Thanks so much @tamerj1 

[tamerj1]

@Anonymous 
Most welcome and thank you for marking my reply as acceptable solution. Please let me if you need any further support. Have a great day!

[Anonymous]

The added good news: I've just applied this to the full data set (200 stores x 14,000 skus x 2 months) and created a couple of large reports, everything operating quickly. 

[tamerj1]

OMG! you made me feel so proud of myself 🙂 Also happy for you

[tamerj1]

@Anonymous 
Just a question. Which Day Lost measure did you choose? I'am just trying to undestand the meaning of this calculation and I would realy appreciate if you help me out understand it 

[Anonymous]

I'm using the one which relates to 'Last Sales Date' rather than 'FIrst Lost Sales Date"

Either could be argued to be correct in this scenario:
- 'Last Sales Date' says "This item has not sold since XX/XX/XX"
- 'First Lost Sales Date reflects the forecast (Lost Sales), so omits any leading days where the forecast was 0 (and therefore Lost Sales was null)

[tamerj1]

Maks sense. Thank you so much for the clarification 🙂

[Anonymous]

This is the current v target result

[Anonymous]

Thanks Ibendlin, just changed to a OneDrive link - does it work?

[lbendlin]

Yes, thank you. Checking the file. Two questions:

1. your expected output seems to be grouped by month.  How are you planning to calculate "Days since last sale"  in that scenario? Might not be meaningful.

2. You say that "Lost sales"  is a precalculated measure.  There is no such thing. Precalculated = column.  Calculated on the fly = measure.  Can you please explain the logic again for this field?

[Anonymous]

Hi Ibdenlin,

Thanks for the note.

1) I'm not sure why you say by month? The date field is date, and the target output is 'Number of Days since last sale' as shown in the screen shot
2) By pre-calculated I mean it was calculated by an external system and is flat data within Power BI. It reflects expected sales

[lbendlin]

Ah, we are looking at the date format differently. I assumed MM/DD/YYYY - my bad.