Forum Discussion
Highlight Changes Week Over Week
Hello.
I'm developing a project that involves taking an existing Excel spreadsheet that is manually generated using a database and converting it into Power BI. For the first iteration, I'd like to stay as true to the original as possible. To accomplish this, I need to be able to highlight each difference from one week to the next.
My data comes from a database as a consistant time each week. The source file is used to assign a week number, and the data is then appended to all of the older data. I have dax formulas that identify 'this week' and 'last week'.
Where do I go from here?
(in the example below, I indicated what I would like highlighted with ().)
| What my table looks like | ||||
| Week Number | Scope | Complete | Accepted | Manager |
| 10 | 222 | 2/22/2021 | 3/15/2021 | Mary |
| 10 | 225 | 3/8/2021 | 3/29/2021 | Mary |
| 10 | 228 | 6/12/2021 | 7/3/2021 | Walter |
| What I want | ||||
| Week Number | Scope | Complete | Accepted | Manager |
| 10 | 222 | 2/22/2021 | 3/15/2021 | Mary |
| 10 | 225 | 3/8/2021 | 3/29/2021 | (Mary) |
| 10 | 228 | (6/12/2021) | (7/3/2021) | Walter |
| My data | |||||||
| Week Number | Activity | Scope | Description | Start | Finish | Step | Manager |
| 8 | 1.1 | 222 | Get money | 12/24/2020 | 1/1/2021 | Mary | |
| 8 | 1.2 | 222 | Build | 2/7/2021 | 2/15/2021 | Mary | |
| 8 | 1.3 | 222 | Complete | 2/14/2021 | 2/22/2021 | CC | Mary |
| 8 | 1.4 | 222 | Accept | 3/7/2021 | 3/15/2021 | OA | Mary |
| 8 | 2.1 | 225 | Get money | 12/12/2020 | 12/20/2020 | Dan | |
| 8 | 2.2 | 225 | Build | 1/25/2021 | 2/2/2021 | Dan | |
| 8 | 2.3 | 225 | Complete | 2/28/2021 | 3/8/2021 | CC | Dan |
| 8 | 2.4 | 225 | Accept | 3/21/2021 | 3/29/2021 | OA | Dan |
| 8 | 3.1 | 228 | Get money | 2/22/2021 | 3/2/2021 | Walter | |
| 8 | 3.2 | 228 | Build | 4/1/2021 | 4/9/2021 | Walter | |
| 8 | 3.3 | 228 | Complete | 4/28/2021 | 5/6/2021 | CC | Walter |
| 8 | 3.4 | 228 | Accept | 5/7/2021 | 5/15/2021 | OA | Walter |
| 9 | 1.1 | 222 | Get money | 12/24/2020 | 1/1/2021 | Mary | |
| 9 | 1.2 | 222 | Build | 2/7/2021 | 2/15/2021 | Mary | |
| 9 | 1.3 | 222 | Complete | 2/14/2021 | 2/22/2021 | CC | Mary |
| 9 | 1.4 | 222 | Accept | 3/7/2021 | 3/15/2021 | OA | Mary |
| 9 | 2.1 | 225 | Get money | 12/12/2020 | 12/20/2020 | Dan | |
| 9 | 2.2 | 225 | Build | 1/25/2021 | 2/2/2021 | Dan | |
| 9 | 2.3 | 225 | Complete | 2/28/2021 | 3/8/2021 | CC | Dan |
| 9 | 2.4 | 225 | Accept | 3/21/2021 | 3/29/2021 | OA | Dan |
| 9 | 3.1 | 228 | Get money | 2/22/2021 | 3/2/2021 | Walter | |
| 9 | 3.2 | 228 | Build | 4/1/2021 | 4/9/2021 | Walter | |
| 9 | 3.3 | 228 | Complete | 4/28/2021 | 5/6/2021 | CC | Walter |
| 9 | 3.4 | 228 | Accept | 5/7/2021 | 5/15/2021 | OA | Walter |
| 10 | 1.1 | 222 | Get money | 12/24/2020 | 1/1/2021 | Mary | |
| 10 | 1.2 | 222 | Build | 2/7/2021 | 2/15/2021 | Mary | |
| 10 | 1.3 | 222 | Complete | 2/14/2021 | 2/22/2021 | CC | Mary |
| 10 | 1.4 | 222 | Accept | 3/7/2021 | 3/15/2021 | OA | Mary |
| 10 | 2.1 | 225 | Get money | 12/12/2020 | 12/20/2020 | Mary | |
| 10 | 2.2 | 225 | Build | 1/25/2021 | 2/2/2021 | Mary | |
| 10 | 2.3 | 225 | Complete | 2/28/2021 | 3/8/2021 | CC | Mary |
| 10 | 2.4 | 225 | Accept | 3/21/2021 | 3/29/2021 | OA | Mary |
| 10 | 3.1 | 228 | Get money | 2/22/2021 | 3/2/2021 | Walter | |
| 10 | 3.2 | 228 | Build | 4/1/2021 | 4/9/2021 | Walter | |
| 10 | 3.3 | 228 | Complete | 6/4/2021 | 6/12/2021 | CC | Walter |
| 10 | 3.4 | 228 | Accept | 6/25/2021 | 7/3/2021 | OA | Walter |
Thanks for your help.
Anonymous There's probably a more efficient way but my brain capacity isn't coming up with it today.
You can create a measure for each column/measure value you want to compare and add conditional formatting. I have done the Manager one for you, just follow the pattern for the Complete and Accepted:
Week on Week Manager Change =VAR _ThisWeek = MAX('Table'[Week Number])VAR _Lastweek = _ThisWeek -1VAR _valThisWeek =[Manager Name]VAR _valLastWeek = CALCULATE([Manager Name], 'Table'[Week Number] = _Lastweek)RETURNIF(_valThisWeek = _valLastWeek, 0, 1)PBIX file attached below signature.
2 Replies
- AllisonKennedy
Community Champion
Anonymous There's probably a more efficient way but my brain capacity isn't coming up with it today.
You can create a measure for each column/measure value you want to compare and add conditional formatting. I have done the Manager one for you, just follow the pattern for the Complete and Accepted:
Week on Week Manager Change =VAR _ThisWeek = MAX('Table'[Week Number])VAR _Lastweek = _ThisWeek -1VAR _valThisWeek =[Manager Name]VAR _valLastWeek = CALCULATE([Manager Name], 'Table'[Week Number] = _Lastweek)RETURNIF(_valThisWeek = _valLastWeek, 0, 1)PBIX file attached below signature.