Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HelloPowerBi
Frequent Visitor

Help with Same period Sales by Store Level

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 CodeStore NameDateNet Sales
100ABC1/1/201850

 

2017 Sales Table 

Store CodeStore NameDateNet Sales
100ABC1/1/2017100

 

 

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!

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.