Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am new to Power BI and am struggling with trying to find the prior year sales by store level.
Currently I have got 2 tables: 2018 & 2017 Sales Table, each with the details of Store Unique ID, Store Name, Daily sales for the particular day in 2018 and 2017 respectively. Each table will have daily sales for each store and there are more than 200 stores in each table.. Also a store opened in 2018 would not have the data in 2017 sales table.
2018 Sales Table
Store Code | Store Name | Date | Net Sales |
100 | ABC | 1/1/2018 | 50 |
2017 Sales Table
Store Code | Store Name | Date | Net Sales |
100 | ABC | 1/1/2017 | 100 |
I tried this formula when i created measure but it doesnt seem to work
PY Sale = CAlculate(SUM('2017'[Net Sales Total]),SAMEPERIODLASTYEAR('2018'[Date]),'2018'[Store Code]=VALUES('2017'[STORE CODE]))
Thanks!
Solved! Go to Solution.
Hi @HelloPowerBi,
You need to UNION two tables (2017 & 2018) as a single one.
Create a calendar table. Establish a one to many relationship from calendar table to the new union table based on date field.
Make a reference to calendar date column rather than '2018'[Date].
Here is an example for your reference:
How to display sales YTD and sales last YTD over years?
Regards,
Yuliana Gu
Hi @HelloPowerBi,
You need to UNION two tables (2017 & 2018) as a single one.
Create a calendar table. Establish a one to many relationship from calendar table to the new union table based on date field.
Make a reference to calendar date column rather than '2018'[Date].
Here is an example for your reference:
How to display sales YTD and sales last YTD over years?
Regards,
Yuliana Gu
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |