Forum Discussion

rustypan's avatar
rustypan
Frequent Visitor
3 years ago
Solved

Difference Between Dates Based on

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...
  • rustypan's avatar
    rustypan
    3 years ago

    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's avatar
rustypan
Frequent Visitor
3 years ago

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's avatar
rustypan
Frequent Visitor
3 years ago

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?