Display records to show status change week over week

[jenmar2023]

Hi - my data is refreshed every week.  How do I track and display records where the status (column) changed from New last week to Pending this week? 

Ex: 

Date	ID	Description	Status
7/17/2023	1	AAAAA	New
7/17/2023	1	BBBBB	New
7/31/2023	1	AAAAA	Pending
7/31/2023	2	CCCCC	New

I want my results to only show the record with ID #1. 

Thank you.

[jenmar2023]

What does the 2 indicate in your formula?  Is there a way to make this dynamic so it tracks more statuses?  It could go from Pending to Completed or New to Completed. 

Thank you

[eliasayyy]

2 means that the start of week is on monday 1 means start of week is sunday, and can you explain more yoir expected results please 

[jenmar2023]

My week will always start on a Monday and there are about 7 different status and ID goes through in it's workflow.  An ID can stay in a status for weeks and then change to a different status in 8 weeks. I need my result to display this RECORD that recently changed.

[eliasayyy]

So technically you want that if a status changes per id , it shows what it was before? Im sorry if im not understanding you well . It would greatly help to show an expected result table

[jenmar2023]

I wanted the expected table to give me the most recent record that had the change. Original: Date ID Description Status 7/17/2023 1 AAAAA New 7/17/2023 1 BBBBB New 7/31/2023 1 AAAAA Pending 7/31/2023 2 CCCCC New Expected outcome to look like this: Date ID Description Status 7/31/2023 1 AAAAA Pending

[jenmar2023]

I wanted the expected table to give me the most recent record that had the change.

Original:

Date ID Description Status

7/17/2023 1 AAAAA New

7/17/2023 1 BBBBB New

7/31/2023 1 AAAAA Pending

7/31/2023 2 CCCCC New

Expected outcome to look like this:

Date ID Description Status

7/31/2023 1 AAAAA Pending

[v-yueyunzh-msft]

Hi ,  @jenmar2023 

Accoridng to your description, your data is refreshed every week. You want to judge the data that has changed in the latest STATUS based on ID. Because the status of ID = 1 has changed last week, it needs to be displayed; Status of ID = 2 does not exist last week, so it is not displayed.

If this , here are the steps you can refer to :
(1)This is my test data:

(2)We can create  a measure like this:

Measure = var _pre_week = CALCULATE( MAX('Table'[Status]) , 'Table'[ID]=MAX('Table'[ID]) , 'Table'[Date]<MAX('Table'[Date]),ALL())
return
IF(_pre_week=BLANK() , BLANK(), IF(MAX('Table'[Status]) <> _pre_week , 1,BLANK()))

Then we can put this measure on the "FIlter on this visual":

 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jenmar2023]

I get an error that the expression refers to multiple columns. Multiple columsn cannot be converted to a scalar value

[jenmar2023]

I'll try it out and let you know. Thank you!

[v-yueyunzh-msft]

Hi ,  @jenmar2023 

Thank you for your reply. According to your description, this dax code is reported as an error on your side. Can you give me some test data that produces this error? Then provide me with the data you want to output in tabular form. Or you can also create a sample.pbix file containing this error message, then upload it to OneDrive, and give me the link share, so that I can help you better~

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[eliasayyy]

If( WEEKNUM(TODAY(),2) = WEEKNUM(MAX([Date]),2), "Pending","New")