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 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
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.
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
@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
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
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |