Unchanged Status over multiple weeks

[jiji]

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 appreciate your help.

Data:

NAME          Week1       Week2      Week3       Week4        Week5        Week6
John             A                  B              C                C                C                  C
Ray               B                  B              B                 B                B                  B
Mary            A                  B               C                 D               E                  F

GOAL:
Name        STATUS       COUNT
John            C                 4
Ray              B                 6

[v-luwang-msft · Accepted solution]

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

[Ashish_Mathur]

Hi,

For the row of Mary, if there were 3 C's and 3 F's, then what result would you expect?

[jiji]

Hello Ashish, Thank you for your message. In that scenario, I would only be interested on the latest dates unchanged status. Say:
NAME   W1   W2   W3    W4   W5   W6
MAR     C       C      A      A       F      F

GOAL IS 
NAME      STATUS       COUNT
MARY         F                  2

I hope this makes sense. My data in the back end is the typical: Name, Date, Status Columns.
Then in the dashboard I just created a matrix table so that I could place Date as columns then count the number of status.