Forum Discussion

jiji's avatar
jiji
Frequent Visitor
3 years ago
Solved

Unchanged Status over multiple weeks

Hi guys, i have a table with 3 columns; Names, Status, date(8 weeks). I want to return the names with unchanged status for the past weeks. Do you have any idea how to build it in dax? Really appre...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    3 years ago

    Hi jiji ，

    Pls refer the below:

    1.unpovit:

    2.use the below dax to create new column:

    WEEK2 = RIGHT('Table 2'[Attribute],1)
    COUNT = CALCULATE(COUNT('Table 2'[Value]),FILTER(ALL('Table 2'),'Table 2'[Name]=EARLIER('Table 2'[Name])&&'Table 2'[Value]=EARLIER('Table 2'[Value])))
    COUNT2 = 'Table 2'[COUNT]+0.1*'Table 2'[WEEK2]
    TEST = MAXX(FILTER('Table 2','Table 2'[Name]=EARLIER('Table 2'[Name])),'Table 2'[COUNT]+0.1*'Table 2'[WEEK2])

     

     

    3.create new table:

    Table 3 = SELECTCOLUMNS(FILTER('Table 2','Table 2'[COUNT2]='Table 2'[TEST]),"Name", 'Table 2'[Name],"Status",'Table 2'[Value],"count",'Table 2'[COUNT])

    Output result:

     

     

    Best Regards

    Lucien

v-luwang-msft's avatar
v-luwang-msft
Community Support
3 years ago

Hi jiji ，

Pls refer the below:

1.unpovit:

2.use the below dax to create new column:

WEEK2 = RIGHT('Table 2'[Attribute],1)
COUNT = CALCULATE(COUNT('Table 2'[Value]),FILTER(ALL('Table 2'),'Table 2'[Name]=EARLIER('Table 2'[Name])&&'Table 2'[Value]=EARLIER('Table 2'[Value])))
COUNT2 = 'Table 2'[COUNT]+0.1*'Table 2'[WEEK2]
TEST = MAXX(FILTER('Table 2','Table 2'[Name]=EARLIER('Table 2'[Name])),'Table 2'[COUNT]+0.1*'Table 2'[WEEK2])

 

 

3.create new table:

Table 3 = SELECTCOLUMNS(FILTER('Table 2','Table 2'[COUNT2]='Table 2'[TEST]),"Name", 'Table 2'[Name],"Status",'Table 2'[Value],"count",'Table 2'[COUNT])

Output result:

 

 

Best Regards

Lucien