Forum Discussion
Getting Last Year Sales grouped by Store
I am having an issue with with displaying last year sales data that are grouped by their respective store locations.
I created the Measure LY Total Sales:
LY Total Sales = CALCULATE(sum(DailySales[Sales]),SAMEPERIODLASTYEAR(DailySales[DateKey]))
But when I used it in a Column chart distributed by the store location I get a chart that is the same level across all stores. Is there a way to properly represent each store's last year sales?
Hi Kashsks,
In your scenario, the issue can be caused by that the chart values are grouped for each X-axis group instance. You need to add a filter within the measure to filter corresponding values for each X-axis instance. Please follow the steps below:
1. Create two test tables.
Sales Record 2015:
Sales Record 2016:
2. Add a measure to Sales Record 2016 table and create the chart.
LY Total Sales = CALCULATE(SUM('Sales Record 2015'[Amount]),SAMEPERIODLASTYEAR('Sales Record 2016'[Date]),'Sales Record 2015'[Company]=VALUES('Sales Record 2016'[Company]))If above is not help, could you provide the data struct about your tables?
Regards,
Xiaoxin Sheng
2 Replies
Hi Kashsks,
In your scenario, the issue can be caused by that the chart values are grouped for each X-axis group instance. You need to add a filter within the measure to filter corresponding values for each X-axis instance. Please follow the steps below:
1. Create two test tables.
Sales Record 2015:
Sales Record 2016:
2. Add a measure to Sales Record 2016 table and create the chart.
LY Total Sales = CALCULATE(SUM('Sales Record 2015'[Amount]),SAMEPERIODLASTYEAR('Sales Record 2016'[Date]),'Sales Record 2015'[Company]=VALUES('Sales Record 2016'[Company]))If above is not help, could you provide the data struct about your tables?
Regards,
Xiaoxin Sheng
Hi Xioxin,
Your suggestion worked perfectly! Thank you so much!
I didn't create 2 tables (since both this year and last year's data sit on the same table), but the concept should be just the same:
LY Total Sales = CALCULATE(SUM(DailySales[Sales]), SAMEPERIODLASTYEAR(DailySales[trxdate]),DailySales[STORE] = VALUES(DailySales[STORE]))
Cheers~
