Creating a DAX measure that Finds the most recent (Start Date) out of stock date

DAX DAX Calculations
1

I am trying to improve this measure where if no SKU is selected it only turns say the top 1st relevant SKU: To be more clearer about what the below measure is doing it will find the Start date when the SKU reached 0 example:

SKU Date On-Hand Quantity test at 0
1234 8/23/23 5000 8/28/23
1234 8/28/23 0 8/28/23
1234 8/29/23 0 8/28/23
1234 8/30/23 2000 8/28/23

Notice in this example it is NOT 8/29/23 because it reached zero on the 28th which is the start date of when it reached 0.

The main issue I am having is when no SKU is selected I get a over 1,000,000,000 rows reached exception and I want to default it to the first SKU that is in this requirement. The user confirmed that they look up a single SKU at a time however it could be helpful if they were to recieve say the first 50 SKUS.

I also tried using the RANK function in conjunction with the TOPN to no avail: Here is my working Code

Test at 0 =
//default to today if no date is selected, force a sku and warehouse to be chosen
VAR selected_date =
IF (
HASONEVALUE ( ‘Date’[Date] ),
SELECTEDVALUE ( ‘Date’[Date] ),
TODAY ()
)
VAR selected_warehouse =
IF (
SELECTEDVALUE ( ‘Warehouse’[Code] )
<> BLANK (),
SELECTEDVALUE ( ‘Warehouse’[Code] ),
“”
)
VAR selected_sku =
IF (
SELECTEDVALUE ( ‘Product’[SKU] )
<> BLANK (),
SELECTEDVALUE ( ‘Product’[SKU] ),
“”
) //obtain the most recent (max date) where the on hand quantity = 0 for that SKU
VAR last_date_zero =
CALCULATE (
MAX ( ‘Date’[Date] ),
CROSSFILTER ( ‘Date’[Date Key], ‘RSC Inventory’[dimAsOfDateKey], BOTH ),
‘RSC Inventory’[On_Hand_Quantity] = 0,
‘Date’[Date] <= selected_date
&& selected_date <= TODAY (),
‘Product’[SKU] = selected_sku,
‘Warehouse’[Code] = selected_warehouse
) //obtain the most recent (max date) where the on hand quantity is NOT 0 for that SKU
VAR last_date_not_zero =
CALCULATE (
MAX ( ‘Date’[Date] ),
CROSSFILTER ( ‘Date’[Date Key], ‘RSC Inventory’[dimAsOfDateKey], BOTH ),
‘RSC Inventory’[On_Hand_Quantity] <> 0,
‘Date’[Date] < last_date_zero,
‘Date’[Date] <= selected_date
&& selected_date <= TODAY (),
‘Product’[SKU] = selected_sku,
‘Warehouse’[Code] = selected_warehouse
) //if the on hand quantity never reaches 0 up to the selected date, output a blank
//if the SKU came into the database (06/29/2023) as out of stock and the logic is trying to grab that day, output a blank since we don’t know the actual out of stock start date
//otherwise add one day to the most recent day where the on hand quantity is NOT 0
VAR result =
IF (
last_date_zero = BLANK ()
|| last_date_not_zero = BLANK (),
BLANK (),
last_date_not_zero + 1
)
RETURN
result

2

Hey @ThreeEleven
It would be helpful if you could share a working PBX file with sample data along with desired output.

however based what i understood from the scenario described , here are my 2 observations

  1. Your scenario is based on " when no SKU is selected" or “When no date is selected”. Consider giving a single select option to the users where they always have atleast 1 date or 1 sku selected by default.
    this is based on the fact that you mention, that your report user always sees 1 sku at a time.

  2. if you have a lots of data Consider creating a summary table with most recent dates per sku. This way, you can reduce the number of rows any of your measure have to itirate on the fly.

I hope this helps.

Regards.
Japjeet

3

Hi @ThreeEleven - Check if below solution works for you. I have added an Index Column based on SKUs using Power Query as below. Also, created a Duplicate of SKU column for display in Visual purpose.

image
image1030×514 18.2 KB

Created a measure as below and applied on the visual.

Filter Logic = if(ISFILTERED('Table'[SKU]),1,if(max('Table'[Index]) = 1,1,0))

Created a slicer for SKU. Based on the Selection, if nothing is selected it will display data for Index = 1 else for SKU’s selected in SKU slicer. This logic can be modified as per requirment.

Let me know if it helped.

ThreeEleven.pbix (58.7 KB)

Thanks
Ankit J

4

That was the other option that I raised when speaking to the data engineers that we may need to create a backend column to handle this. Thank you for the effort and I will mark it as the solution.

Again,thank you very much!