Getting Last Year Sales grouped by Store

[Kashsks]

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?

[v-shex-msft · Accepted solution]

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

[Kashsks]

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~