How to highlight changed data and compare what's changed from previous week

[Anonymous]

Hi Everyone,

I am looking for ideas to implement something where users can see what's changed over previous week in terms of data. 

For example, let's say I have a table below that shows recent most data. 

Category	Price
Diamond	45
Gold	50
Silver	100

Let's say, a week before a data was like below. 

Category	Price
Diamond	50
Gold	100
Silver	25

So now, how can I show data that includes columns: Category, Price from last week, price this week and then highlight the one that went up in Green, the one that went low, highlight that in red. Basically, I am trying to see ways and ideas to show data points from current week with previous week and highlight what's changed. Any solutions or thoughts would be super helpful, thanks in advance. 

[amitchandak · Accepted solution]

@Anonymous , do you have this week and previous week data in one table with week number or do want to create a snapshot in power bi.

If you have both weeks in table, then create a table with unique week year (all) and create a rank in that and try measure like the example

//Say week year table is date

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures

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

You can take a diff.

In case you need to create a snapshot in power bi

refer

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Data:

1. to create a calculated column(week num):

WeekNum = WEEKNUM('Table'[Date],1)

2. to get the max weeknum with  a  measure:

_Max_WeekNum = CALCULATE(MAX('Table'[WeekNum]),ALL('Table'))

3. to create 2 calculate table(this week, last week):

W1 = FILTER('Table','Table'[WeekNum]=[_Max_WeekNum])

W2 = FILTER('Table','Table'[WeekNum]=[_Max_WeekNum]-1)

4. to show category and price of last week with a table visual

5. to get the price of this week with a measure:

_W1_price = LOOKUPVALUE(W1[Price],W1[Category],MAX('W2'[Category]))

6. to highlight the value with a color measure:

_color = IF(MAX('W2'[Price])<[_W1_price],"green","red")

Model:

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Anonymous]

@amitchandak thanks so much for taking the time to share solution. Basically, I dont have a week number but just a date field with value tagged with each row. Can you please suggest how I can calculate in this case.

[amitchandak · Accepted solution]

@Anonymous , do you have this week and previous week data in one table with week number or do want to create a snapshot in power bi.

If you have both weeks in table, then create a table with unique week year (all) and create a rank in that and try measure like the example

//Say week year table is date

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures

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

You can take a diff.

In case you need to create a snapshot in power bi

refer

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/