SSS - Sames Sales Store

[Anonymous]

Hello everybody ! How are you?

I have 3 tables:
- F_Sales
- D_Stores
- D_Dates

In the D_Stores table, I have all my stores and an opening_date and closed_date column.

I need to make a measurement in which according to the month you select I compare the sales of the same stores that were open for the previous period.

For example, if I select the month of October 2019 I have 50 stores open, but in October 2018 I had 80, I need you to compare only the sales of the 50 open.

What do you recommend me to do?

Thanks !

Regards!

[V-lianl-msft]

Hi @Anonymous ,

Based on your description, if you want to compare the sales of the same store in the same period last year, you should describe the structure of the "F_Sales" table. It is better to provide some sample data.

Best Regards,

Liang 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @V-lianl-msft !

What I have to do is compare the sales of one year's stores vs. the sales of the previous year.
But if a store wasn't open, you shouldn't consider it anymore.

Basically, what I do is this (but it's not the right way):

Sales 2018: IF(AND(Store1Sales2018 > 0, Store1Sales2019> 0, Store1Sales2018, 0)

Sales 2019: IF(AND(Store1Sales2018 > 0, Store1Sales2019> 0, Store1Sales2019, 0)

The problem is when I select the whole year, I must compare month to month that the store has sales, then with IF would not be the best way.

The table of sales is related to that of stores through the ID_Store and is related to that of date through Dates.

Regards!

[V-lianl-msft · Accepted solution]

Hi @Anonymous,

First, you need to create a calendar table as slicer, and then create two measures to calculate the total sales of each store in the selected year and last year. you can refer to this .pbix

Best Regards,

Liang 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...