Forum Discussion

jlankford's avatar
jlankford
Advocate I
8 years ago
Solved

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 ha...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi jlankford,

     

    Download my revised solution from the same link i shared above.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Could you show your exact result that you are expecting?

  • jlankford's avatar
    jlankford
    Advocate I
    8 years ago

    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. 

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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

      • jlankford's avatar
        jlankford
        Advocate I
        8 years ago

        Thank 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).