Difference Between Dates Based on

[rustypan]

Hi - I have the following data, where Rolling Inventory Level is a measure and the rest are columns from an excel input. I want to calculate difference between dates (in days) depending on conditions as follows .... (a) take the earliest or first date from Week End (Fri) column for which Rolling Inventory Level is < 0 (eg 17 March 2023 in this case) and then subtract the number of days between that selected date (17 March 2023 in this case) and the earliest date in the Week End (Fri) column (04 November 2022 in this case). The alternative (b) is when Rolling Inventory never goes below 0, take the last date in Week End (Fri) column and calculate the number of days between that last date and the first date in Week End (Fri) column.  

When I write the if Rolling Inventory Level < 0, min Week End (Fri) etc that logic returns the earliest Week End (Fri) value in the entire list, not the earliest for which Rolling Inventory < 0 ... How do I get round this? Thanks for any help.

[rustypan · Accepted solution]

Thought it may help others so posting the solution I came to ... maybe this is super long winded but something is better than nothing, especially when something does the job! 🙂

1. Created a measure to extract the earliest date in the table

Earliest Date = FIRSTDATE('Table'[Week End (Fri)])

2. Created a measure to extract the first date of negative inventory

First NegInv Wk = MINX(FILTER('Table', 'Table'[Inventory Total End of Wk] < 0), 'Table'[Week End (Fri)])

3. Created a measure to extract the latest date in the table

Max Inv Wk = MAX('Table'[Week End (Fri)])

4. Created a measure to decide which value out of Max Inv Wk and First NegInv Wk to use (to later compare against the Earliest Date measure)

Inv Wk to Use = if(ISBLANK([First NegInv Wk]), [Max Inv Wk], [First NegInv Wk])

5. Finally! Compare the chosen week from step 4 with the Earliest Date measure from step 1

Days to Zero Stock = DATEDIFF([Earliest Date], [Inv Wk to Use], DAY)

[rustypan]

It seems as though the first filter, before Calculate, is working properly. But the filter conditions after Min don't seem to be having any effect as the first (min) date in the entire table is being returned (for me) rather than the first date at which Rolling Inventory < 0

[rustypan]

Progresss! I took away the "ALL" part in the filters and now I get the corresponding date at which there is a negative Rolling Inventory value. How can I now extract the first date? As this is a measure, I could not use firstdate function?

[rustypan]

In the Comp & Comp Desc columns, there are many component IDs (Comp) and their description (Comp Desc) and I have a slicer that is used to select a single component to view Inventory status for that component only - which is the table visual I posted a picture of. Could this slicer be a reason why the solution does not work for me? 

[v-zhangti]

Hi, @rustypan 

Please refer to the attached method.

Min Week End = 
IF (
    [Rolling Inventory Level] < 0,
    CALCULATE (
        MIN ( 'Table'[Week End] ),
        FILTER ( ALL ( 'Table' ), [Rolling Inventory Level] < 0 )
    )
)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rustypan]

Hi @v-zhangti - Thank you for taking a look at my issue. I tried what you said but it doesn't quite work for me ... I get the earliest date posted next to the values where Rolling Inventory < 0 

[rustypan]

PS Week End (Fri) is a date format column