Forum Discussion

jcastr02's avatar
jcastr02
Icon for Post Prodigy rankPost 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  -...
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"
    )

     

     

Fowmy's avatar
Fowmy
Icon for Super User rankSuper 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"
    )