Forum Discussion
Finding repeated values over consecutive dates
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,
LydiaThank you so much - this works, but too well.
We have been selling items for 90 days. Unfortunately, the data we download is done in 365 day groupings. As a result, we have 275 days of items that are 0 sales, so every item comes up with an alert.
I thought that a visual level filter might fix it, but I'm guessing that due to the index and group columns, we are able to calculate past the last 90 days.
Is there a way to limit my view to 90 days since a visual level filter isn't cutting it?
(Also - I need to reiterate - I'm VERY grateful for your help and patience).
jlankford,
I am not very clear about your requirment, do you mean that limit the dates in Matrix to show only 90 days? If so, you can filter dates in visual level filter.
If visual level filter doesn't help, please post complete data of your table and post expected result in screenshot.
Regards,
Lydia