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.
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.
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.
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))
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:
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?
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.
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.
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.