Forum Discussion
SSS - Sames Sales Store
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!
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.
4 Replies
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-TITHW/m-p/434008Hi 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.
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!
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.
