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