Flag a record by count back days

[jmolina]

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!

[v-shex-msft]

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

[Greg_Deckler]

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

[jmolina]

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 · Accepted solution]

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)