Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
keewei87
Frequent Visitor

Week on Week Percentage Change in chart

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 VisitCustomer NameCustomer Paid
1/1/2022Customer AYes
4/1/2022Customer BNo
7/1/2022Customer CYes

 

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:

keewei87_1-1643028373336.png

 

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

keewei87_0-1643028356382.png

 

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!!!

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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)

vjingzhang_0-1643771751146.png

 

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

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

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)

vjingzhang_0-1643771751146.png

 

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

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

goncalogeraldes
Super User
Super User

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

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)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors