Forum Discussion
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...
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 tableMax 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 1Days to Zero Stock = DATEDIFF([Earliest Date], [Inv Wk to Use], DAY)
PS Week End (Fri) is a date format column