Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Proud to be a Super User!
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))
Proud to be a Super User!
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?
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/
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Proud to be a Super User!
Also make sure you are filtering your page by the year column from your date table. It appears that it should by Sys_Year.
Data on Rows or Filters, as a rule of thumb, should come from your look up tables and not your fact table.
Proud to be a Super User!
oh wow, it works now, perfect. I am still getting used to the working of Power BI.
I think the catch was > "Date on Rows or Filters - should come from look up tables and not your fact table."
One last question -
Like we have the previous week sales from this year, how do I look at the Previous to Previous week sales same this year.
Example. Currently in 2017-w30, then Compare 2017-w29 verses 2017-w28.
So I already have one the measure(as below) calculated, how do I calculate the the other one.
Sales This Year = CALCULATE([Total Sales], DATEADD(Calender[Date_dt], -7, Day))
Thank you again for your help, appreciate it.
Proud to be a Super User!
Now I got both my visuals working -
1. Sales for previous week This Year verses Sales for same week Last Year and variance
2. Sales for previous two weeks and variance
Refer below image, so if I filter on 2017-w31 then I get first visual correct but second wrong,
while if I filter on 2017-w30 then I get second visual correct while first wrong.
As we are currently in 2017-w31
1. Compare 2017-w30 vs 2016-w30
2. Compare 2017-w30 vs 2017-w29
I tried changing the measure but I am not able to get what should I change so that both visuals can sync up.
I dont want to show complete table, I only want to show the relevant data as above stated.
Below measure used -
Total Sales = sum(SalesTable[Sales])
Sales Last Year = CALCULATE([Total Sales], DATEADD(DATEADD(Calender[Date_dt], -7, Day), -1, YEAR))
Sales This Year = CALCULATE([Total Sales], DATEADD(Calender[Date_dt], -7, Day))
Variance Sales = [Total Sales] - SalesTable[Sales This Year]
Variance = SalesTable[Sales Last Year] - SalesTable[Sales This Year]
Edit:Below shown are the numbers for sample sales numbers -
Proud to be a Super User!
I did update based on your suggestion but still same issue.
Hi @Visuals_PowerBI,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
Hi @Visuals_PowerBI,
I think we don't need time intelligence functions in your scenario. Maybe you could try this.
SameWeekLastYear = VAR ContextYear = MIN ( 'SalesTable'[Sales_Year] ) VAR ContextWeek = MIN ( 'SalesTable'[Sales_Week] ) RETURN CALCULATE ( SUM ( SalesTable[Sales] ), FILTER ( ALL ( SalesTable ), SalesTable[Sales_Year] = ContextYear - 1 && SalesTable[Sales_Week] = ContextWeek ) )
YearVariance = SUM ( SalesTable[Sales] ) - [SameWeekLastYear]
PreviousWeek = VAR ContextDate = MIN ( SalesTable[Sales_WeekEndDate] ) RETURN CALCULATE ( SUM ( SalesTable[Sales] ), FILTER ( ALL ( SalesTable ), 'SalesTable'[Sales_WeekEndDate] = ContextDate - 7 ) )
WeekVariance = SUM ( 'SalesTable'[Sales] ) - [PreviousWeek]
Best Regards!
Dale
Dear Dale,
thank you for the help. It helps me also a lot.
I have still an issue with the total.
When I use the formula Sameweeklastyear the total sum of this line is not correct
The correct sum of the least year week should be 46.796.334 but I have 9.554.492
Could you please give me an advise how should I can get the correct sum.
Thank you for your help.
Br
Feri
Thank you for your response.
Can you tell me how to create the date table? Would it only have one column named Date key?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |