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.

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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

    • jlankford's avatar
      jlankford
      Advocate I
      8 years ago

      Hi, 


      Thank you again for your reply, and sorry for the delay in getting back to you. 

       

      Below is a screenshot. I used a visual filter to only show the dates 9/28/2017 - 10/6/2017

       

      As you can see, we have ample inventory, and no zeroes. Unfortunately, "ALERT" always reads a number (the reason these numbers are higher than "1" is because I have products belonging to each store that I wish to obscure for now). 

       

       

      In this data source, it contains the most recent 180 days. For some products, we have had good inventory, so I get "0" for an alert, which is what we want. However, other products, we did not begin selling until 90 days ago, meaning that we have 90 days before it where inventory reads "0". Even with the visual filter showing only 90 days, "alert" still reads a "1" for these products. 

       

      Thank you again for your help and attention. 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi jlankford,

         

        Whom are you replying to?  If it is to my post, then tell me exactly the scenario where it is not working.