cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kashsks
Regular Visitor

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?

 

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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:

Capture.PNG

Sales Record 2016:

Capture2.PNG

 

 

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]))

 

 Capture4.PNG

 

 Capture5.PNG

 

 

If above is not help, could you provide the data struct about your tables?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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:

Capture.PNG

Sales Record 2016:

Capture2.PNG

 

 

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]))

 

 Capture4.PNG

 

 Capture5.PNG

 

 

If above is not help, could you provide the data struct about your tables?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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~

 

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.