Weekly comparison table

[Sbudd]

Hi 

Is the table below possible to be built in PBI? I can easily build the first 3 columns, but the difference and percent change columns are proving more difficult. I dont mind having it as seperate tables if that is easier. 

It will also be 2 weeks of data. 

Thanks in advance

[rajendraongole1 · Accepted solution]

Hi @Sbudd -To includes the first three columns along with the "Difference" and "Percent Change" columns.

You can create a measure for sales week1

Sales_Week1 =
CALCULATE(
SUM('Sales'[Sales]),
'Sales'[Week] = "Week 1"
)

Week2:

Sales_Week2 =
CALCULATE(
SUM('Sales'[Sales]),
'Sales'[Week] = "Week 2"
)

To find the difference of week1 and week2 and then find the percent change calculation with difference.

Difference = [Sales_Week2] - [Sales_Week1]

Percent_Change =
IF(
[Sales_Week1] = 0,
BLANK(),
([Difference] / [Sales_Week1]) * 100
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Anonymous]

Your solution is so great  @Anonymous 
Hi, @Sbudd 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster. Thank you very much for your kind cooperation!

Best Regards

Jianpeng Li

[Anonymous · Accepted solution]

@Sbudd Here you go, with dynamic format strings.

[rajendraongole1 · Accepted solution]

Hi @Sbudd -To includes the first three columns along with the "Difference" and "Percent Change" columns.

You can create a measure for sales week1

Sales_Week1 =
CALCULATE(
SUM('Sales'[Sales]),
'Sales'[Week] = "Week 1"
)

Week2:

Sales_Week2 =
CALCULATE(
SUM('Sales'[Sales]),
'Sales'[Week] = "Week 2"
)

To find the difference of week1 and week2 and then find the percent change calculation with difference.

Difference = [Sales_Week2] - [Sales_Week1]

Percent_Change =
IF(
[Sales_Week1] = 0,
BLANK(),
([Difference] / [Sales_Week1]) * 100
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!