Return Unchanged status from latest week to prior 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

[amitchandak · Accepted solution]

@jiji , First you have to unpivot the Week in power query

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

https://radacad.com/pivot-and-unpivot-with-power-bi

Duplicate column and split week out using

Split Column By Digit to Non Digit & Non Digit to Digit: https://youtu.be/tY4Yk1crS9s

In DAX Create Rank on week rank column you got now , Make sure you create a new week tbale and do week rank there . and join with you table 

Week Rank RANKX(all('Date'),'Date'[Week],,ASC,Dense)

This Week = CALCULATE(Max('Table'[Status]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(Table'[Status]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Sumx(Values(Table[name]) , calculate( If([This Week] = [Last Week], 1, blank()) ))

or

Sumx(Summarize(Table,Table[name], Date[Week]) , calculate( If([This Week] = [Last Week], 1, blank()) ))