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

[Visuals_PowerBI]

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. 

 

[kcantor]

@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. 

[kcantor]

@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))

[Visuals_PowerBI]

@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?

[kcantor]

@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/

[Visuals_PowerBI]

@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?

[kcantor]

@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.

[Visuals_PowerBI]

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.

[kcantor]

@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.

[kcantor]

@Visuals_PowerBI

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.

[Visuals_PowerBI]

@kcantor

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.

[kcantor]

You only need the one other calculation you listed. ThT is the best thing about powerbi, write once and re use. The measure you just shared is for sales last week. Put total sales on and you are set. For the variance you do week over week variance =[total sales]-[last week total sales].

[Visuals_PowerBI]

@kcantor

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 -

[kcantor]

Your measures could be cleaned up and might fix the issues.


Total Sales = sum(SalesTable[Sales])

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

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

Variance Sales year over year = [Total Sales] - [Sales Last Year]

Variance week over week = [total sales] - [Sales This Year last week]
Sorry if the capitalization is off. I am working on this from my phone.

[Visuals_PowerBI]

I did update based on your suggestion but still same issue. 

[v-jiascu-msft]

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

[v-jiascu-msft]

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

[viferenc]

 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

[Visuals_PowerBI]

@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?