Forum Discussion
Year on Year variance without date column
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
5 Replies
v-qiuyu-msftCommunity Support
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 Yuv-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
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.