Week on Week Percentage Change in chart

[keewei87]

Dear Powerbi Gods,

I new to powerbi and am trying to come out with weekly percentage changes over the week of the year but was unable to find any materials on this.

My database is as appended

Client Visit Record

Date of Visit	Customer Name	Customer Paid
1/1/2022	Customer A	Yes
4/1/2022	Customer B	No
7/1/2022	Customer C	Yes

I have created measures as

Week change = DIVIDE ( Paid per Visit, Total Visit)

Total Visit = Calculate(Count('Client Visit Record' [Index]),All())

Customer Paid per Visit = CALCULATE(COUNT('Client Visit Record'[Customer Name]), FILTER('Client Visit Record', 'Client Visit Record' [Customer Paid]= "Yes"))

My result as of now is:

When I put into bar chart, the results is as follows.

What I want is : 

1. A bar chart showing x axis % change and y axis week of the year.

After this I also want to have 2 cards to give an overview that shows

1. Last Week % change and
2. This Week % change

Thanks in advance!!!

[v-jingzhang · Accepted solution]

Hi @keewei87 

If your [Total Visit] should be the number of visits in a week, and [Customer Paid per Visit] should be the number of paid visits in the same week, you could try below measures. Let me know if I misunderstood it.  

Total Visit = COUNT('Table'[Index])

Paid Visit = COUNTROWS(FILTER('Table','Table'[Customer Paid]="Yes"))

Week Change = DIVIDE([Paid Visit],[Total Visit])

This Week % change = 
var _thisWeek = WEEKNUM(MAX('Table'[Date of Visit]),21)
return
CALCULATE([Week Change],'Date'[Week of Year]=_thisWeek)

Last Week % change = 
var _lastWeek = WEEKNUM(MAX('Table'[Date of Visit])-7,21)
return
CALCULATE([Week Change],'Date'[Week of Year]=_lastWeek)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[keewei87]

Wow!!Thanks!!!
May I know when do i use Var and when do I just countrows instead of count?

[goncalogeraldes]

Hello there @keewei87 ! It looks like the filed you are using in the X axis has no relation with the calculations you have performed? Do you have a calendar table/date dimension? Are you using it in the time intelligence ou time related analysis? 

You can dive a little deeper in these concepts in the following links:

Create a Date Dimension in Power BI in 4 Steps - Step 1: Calendar Columns - RADACAD

Using a Date Dimension Table in Power BI (sqlshack.com)

Time Intelligence in Power BI Desktop - SQLBI

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[keewei87]

Yes. I have a date dimension with list of dates

The week of year is a column created using 

Week of Year = WEEKNUM('Date'[Date],21)