Forum Discussion

jmolina's avatar
jmolina
Frequent Visitor
7 years ago
Solved

Flag a record by count back days

Hi Guys,.

  

I need some help with my DAX. I want to flag a record by count back days, if is the same ean, store, and stock if stock [date] = stock[previous N days].

 

This is a sample data:

 

 

 

 

 

Another example:

 

 

 

 

 

 Could you help me please?

 

Best!

 

General Comment
Need Help
Show and Tell
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    I think that you want something like:

     

    Column = 
VAR __minDate = [date] - 2
VAR __table = FILTER(ALL('Table'),[date]<=EARLIER([date]) && [date]>=__minDate && [store]=EARLIER([store]) && [item]=EARLIER([item]))
VAR __val2day = MAXX(FILTER(__table,[date]=EARLIER([date])-2),[stock qty])
VAR __val1day = MAXX(FILTER(__table,[date]=EARLIER([date])-1),[stock qty])
RETURN
IF(__val2day = [stock qty] && __val1day = [stock qty],1,0)

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    I'm not understanding the logic of when something is 1 or 0.

    • jmolina's avatar
      jmolina
      Frequent Visitor
      7 years ago

      Hi,.

       

       

      I need to flag (1, 0) if the stock quantity maintains static in the last 3 consecutive days, for the same item and store

       

      i have updated my post with other example table. Thanks a lot.

       

       

      Best!

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        7 years ago

        I think that you want something like:

         

        Column = 
VAR __minDate = [date] - 2
VAR __table = FILTER(ALL('Table'),[date]<=EARLIER([date]) && [date]>=__minDate && [store]=EARLIER([store]) && [item]=EARLIER([item]))
VAR __val2day = MAXX(FILTER(__table,[date]=EARLIER([date])-2),[stock qty])
VAR __val1day = MAXX(FILTER(__table,[date]=EARLIER([date])-1),[stock qty])
RETURN
IF(__val2day = [stock qty] && __val1day = [stock qty],1,0)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi jmolina,

     

    If you mean mark tag of same store amount, you can try to use following calculated column formula:

    Continuous Tag =
VAR prev =
    LOOKUPVALUE (
        'Table'[STOCK],
        'Table'[STORE], [STORE],
        'Table'[EAN], [EAN],
        'Table'[Date], [Date] - 1
    )
VAR next =
    LOOKUPVALUE (
        'Table'[STOCK],
        'Table'[STORE], [STORE],
        'Table'[EAN], [EAN],
        'Table'[Date], [Date] + 1
    )
RETURN
    IF ( [STOCK] IN { prev, next }, 1, 0 )

    Regards,

    Xiaoxin Sheng