cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Visuals_PowerBI Frequent Visitor
Frequent Visitor

HELP please, Previous Week comparison for THIS year verses LAST year

I have simple 4 fields in my data, Year number, Week number, Weekend date and Sales measure.

I would like to visualize previous week sales verses last year sales for same week.

Example.

If I am currently into week 30 of year 2017. Then I would like to compare 2017-week 29 verses 2016-week29. and then show the variance of the two values. It should automatically update every week to show the previous week.

Please advice. 

 Capture.JPGCapture1.JPG

18 REPLIES 18
Super User
Super User

Re: HELP please, Previous Week comparison for THIS year verses LAST year

@Visuals_PowerBI

First, add a complete date table and connect your week end date to the date key. I used table name of Fact for your table and Date for the Dtae table you will add.

Total Sales = SUM (Fact[Sales]) 

Total Sales Last Week = CALCULATE([Total Sales], DATEADD(Date[Datekey], -7, Day))

Variance = [Total Sales]-[Total Sales Last Week]

Put your week number on the axis and place the measures as the columns. As long as your date table is not missing any dates this will work. 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Visuals_PowerBI Frequent Visitor
Frequent Visitor

Re: HELP please, Previous Week comparison for THIS year verses LAST year

@kcantor

Thank you for your response.

Can you tell me how to create the date table? Would it only have one column named Date key?

Super User
Super User

Re: HELP please, Previous Week comparison for THIS year verses LAST year

@Visuals_PowerBI

sorry, My mind was reading week over week. Use this for last year:

Total Sales Last Year= CALCULATE([Total Sales], DATEADD(Date[Datekey], -1, Year))



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Visuals_PowerBI Frequent Visitor
Frequent Visitor

Re: HELP please, Previous Week comparison for THIS year verses LAST year

@kcantor

Thank you for your response.

Can you tell me how to create the date table? would this table have only one column named Date key?

Super User
Super User

Re: HELP please, Previous Week comparison for THIS year verses LAST year

@Visuals_PowerBI

You need columns for every granularity you will use.

I keep date, month, year, week number, quarter, and day name in all of mine. I also add custom combos including year-month and year-week for displaying long term data.

Time Intelligence functions need a date table. Be sure you do not skip any dates even if you have to sales data.

Here is a link for building your date table:

https://exceleratorbi.com.au/power-pivot-calendar-tables/



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Visuals_PowerBI Frequent Visitor
Frequent Visitor

Re: HELP please, Previous Week comparison for THIS year verses LAST year

@kcantor

Thank you again for your response,

I  created the Calender table (has all details as you mentioned)

Joined the Fact and Calender table based on Date column.

 

Below measures - 

Total Sales = sum(SalesTable[Sales])

Sales This Year = CALCULATE([Total Sales], DATEADD(Calender[Date_dt], -7, Day))

Sales Last Year = CALCULATE([Total Sales], DATEADD(Calender[Date_dt], -1, YEAR))

Variance = SalesTable[Sales Last Year] - SalesTable[Sales This Year]

 

Q1 - Sales this verses last year show same value and variance as 0. (actually they are not)

Q2 - what does cross filter direction mean? is selecting "both" correct?Capture4.JPG

Capture2.JPG

 

Super User
Super User

Re: HELP please, Previous Week comparison for THIS year verses LAST year

@Visuals_PowerBI

You have the same numbers showing because I forgot to tell you to filter the report to the current year so that it can calculate last year.  My bad. Drop your year field as a report filter and select the current year (or any other single year for which you have data). It will clear that right up.

 

The cross filter direction allows filters to flow between tables. 

Edit: It has been a very long week already.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Visuals_PowerBI Frequent Visitor
Frequent Visitor

Re: HELP please, Previous Week comparison for THIS year verses LAST year

Sorry to know that, but thank you that you are still helping.

Its been two days that I am trying this, multiple soutions but nothing seems to work.

I am not sure, why I can not just add a sample pbix here as an attachment. (Am I missing anything here, this is my first post)

I am only able to share photos. 

 

I have got below, but how do I create a visual which only shows me previous week and last year same week.

last week was 29-2017 , so that verses 29-2016.

 

Capture5.JPG

 

Super User
Super User

Re: HELP please, Previous Week comparison for THIS year verses LAST year

@Visuals_PowerBI

Use your week number from  your date table for the row and drop your variance on as the value.

If you only want to see a single week you will need to filter the report to that week either by a filter in the filter pane or by a slicer.

 

Drop them on the table in this order: Week number (from Date Table), Total Sales measure from Fact Table, and then Sales Last year from Sales table. You can even add the variance or just put the vairance.

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!