Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
2 years ago
Solved

Looking back 4 weeks

Every week I am adding new data if a store is considered HTS (y/n).  How can I add a category for that respective week that will look back on latest 4 entries and categorizes  either
Off>=4 weeks  - Has  been "N" for 4 consecutive weeks
HTS<4 Weeks - Has NOT Been "Y" for 4 consecutive weeks

HTS>=4 Weeks - Has been "Y" for 4 consecutive weeks

See below desired result

StoreWeekHTS?HTS Category (desired result)
1019510/2/2023N 
1019510/9/2023N 
1019510/16/2023N 
1019510/23/2023NOff>=4 Weeks
1019510/30/2023YHTS<4 Weeks
1019511/6/2023YHTS<4 Weeks
1019511/13/2023NHTS<4 Weeks
1019511/20/2023NHTS<4 Weeks
1019511/27/2023YHTS<4 Weeks
1019512/4/2023YHTS<4 Weeks
1019512/11/2023YHTS<4 Weeks
1019512/18/2023YHTS>=4 Weeks
1019512/25/2023YHTS>=4 Weeks
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    jcastr02 

    Add the following calculated column:

    HTS Status = 
VAR __STORE= Table18[Store]
VAR __WEEK= Table18[Week]
VAR __T = 
     FILTER (
        ALL(table18),
        table18[Store] =__STORE &&
        table18[Week] <= __WEEK &&   table18[Week] > __WEEK - 28                
    )
VAR __N4WK =  COUNTROWS( FILTER ( __T, table18[HTS?] = "N" ) )
VAR __Y4WK =  COUNTROWS( FILTER ( __T, table18[HTS?] = "Y" ) )
VAR __YLESS4WK =  COUNTROWS( FILTER ( __T, table18[HTS?] <> "N" ) )
            
RETURN 
   SWITCH(
        TRUE(),
        __N4WK >= 4 , "Off>=4 Weeks",
        __Y4WK >= 4 , "	HTS>=4 Weeks",
        __YLESS4WK < 4 && __YLESS4WK >= 1 , "HTS<4 Weeks"
    )

     

     

1 Reply

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    jcastr02 

    Add the following calculated column:

    HTS Status = 
VAR __STORE= Table18[Store]
VAR __WEEK= Table18[Week]
VAR __T = 
     FILTER (
        ALL(table18),
        table18[Store] =__STORE &&
        table18[Week] <= __WEEK &&   table18[Week] > __WEEK - 28                
    )
VAR __N4WK =  COUNTROWS( FILTER ( __T, table18[HTS?] = "N" ) )
VAR __Y4WK =  COUNTROWS( FILTER ( __T, table18[HTS?] = "Y" ) )
VAR __YLESS4WK =  COUNTROWS( FILTER ( __T, table18[HTS?] <> "N" ) )
            
RETURN 
   SWITCH(
        TRUE(),
        __N4WK >= 4 , "Off>=4 Weeks",
        __Y4WK >= 4 , "	HTS>=4 Weeks",
        __YLESS4WK < 4 && __YLESS4WK >= 1 , "HTS<4 Weeks"
    )