Year on Year variance without date column

[Anonymous]

I have a table with sales data in and a weekly date format like 2018-01, 2018-02, 2018-03 etc.

The week and year data comes through as text and not a date format.

I am trying to do year on year comparisons.

This is what my data looks like...

Year-Week     Sales

2017-01         500

2017-02         500

2017-03         500

...

2018-01         550

2018-02         550

2018-03         550

I want to show 

Year-Week    Sales    LastYearSales  Variance   Variance %

2018-01         550           500                50             10%

2018-02         550           500                50             10%

2018-03         550           500                50             10%

Here is an example of my data with the PreviousYear and LastYear columns created 

[Anonymous]

Hi @Anonymous,

How did you create "LastYear" column from your "Year Week" column. Please let me know.

I am dealing with the same scenario. I have Year-Month-Week column and it is in " 2020-01-W1" format. I am trying to calculate Variance% of Current year(2020-01-W1) and Last year(2019-01-W1). 

Thank you!

Regards,

Aswini C S.

[v-qiuyu-msft]

Hi @Anonymous,

Based on my test, you can refer to below steps:

1. Create calculated columns below:
LastYearSales =
var previousweek = Sales[PreviousWeek]
var saleonpre = CALCULATE(SUM(Sales[Sales]),ALL(Sales),Sales[Year-Week] = previousweek)
return
IF(ISBLANK(saleonpre),0,saleonpre)

Variance = Sales[Sales]-Sales[LastYearSales]

Variance%=DIVIDE( Sales[Variance],Sales[LastYearSales])

2.Create a Table visual in your report and add the [Year-Week], [Sales], [LastYearSales], [Variance], [Variance%] fields.

3.Filter the data that you want to show in the Table visual and you can see the result.

You can also download the PBIX file to have a view.

Best Regards,
Qiuyun Yu

[Anonymous]

@v-qiuyu-msft Thanks for your help I think we are nearly there 

I have created the column 

LastYearRetailSales =
var previous = [LastYear]
var saleonpre = CALCULATE(SUM(MK_PAPA_CREDIT_PENETRATION_REPORTING_SERIES[Retail Sales]),ALL(MK_PAPA_CREDIT_PENETRATION_REPORTING_SERIES),MK_PAPA_CREDIT_PENETRATION_REPORTING_SERIES[Year Week] = previous)
return
IF(ISBLANK(saleonpre),0,saleonpre)

It is nearly doing what I require but the LastYearRetailSales are significantly higher

[Anonymous]

@v-qiuyu-msft

Any ideas? 

[v-qiuyu-msft]

Hi @Anonymous,

Which aggregation set for the LastYearRetailSales column in the table visual? Please share pbix file with us. Do remove sensitive data before removing sensitive data. 

Best Regards,
Qiuyun Yu