Forum Discussion
Finding repeated values over consecutive dates
I hope I can describe this correctly.
I am building a report, and I need to be able to create a column or measure that essentially says "Alert" or "Okay" if a certain condition is met.
I have daily inventory data for the last 90 days. I need to be able to identify any time where we have had zero inventory within a 3 day (or longer) stretch.
Here is an example of what the data looks like:
As you can see, Products "ABC" and "LMN" both have longer than 3-day stretches where there was no inventory on the shelf. I'd like ot create something that creates a column for that product that can tell me that there is an issue and this sku needs to be reordered.
Thank you.
17 Replies
Hi,
Could you show your exact result that you are expecting?
Hi Ashish,
Thank you for your reply.
If I could get a column in the matrix, or any sort of indicator that returns which products have an inventory issue (i.e. 5 zeroes in a row) this would achieve what I want.
One thing to know about the data - it will never be negative and it will never be anything but whole numbers. So if there is a way to add groups of 5 adjacent cells in a row over and over to see if the sum=0, this would achieve it, but I'm unsure of how to do this with DAX or anything else right now.
jlankford,
Create the following columns in your table.
Indexcol = CALCULATE(COUNT(Table[Date]),ALL(Table),FILTER(Table,Table[Type]=EARLIER(Table[Type])&&Table[Date]<=EARLIER(Table[Date])))Group = ROUNDUP(Table[Indexcol]/3,0)
Sumbygroup = CALCULATE(SUM(Table[Value]),FILTER(Table,Table[Type]=EARLIER(Table[Type])&&Table[Group]=EARLIER(Table[Group])))
Alert = IF(CALCULATE(MIN(Table[Sumbygroup]),ALLEXCEPT(Table,Table[Type]))=0,1,0)
Regards,
Lydia
Hi,
Share some data, describe the question and show the expected result.
