Forum Discussion
'Same Store' Utilzation
Hi guys,
I'd like to see the growth of 'same store' volume (same store = not including new customers)
I have the following Table Data:
| CUST SAP Number | RunYearMonth | C17 Volume |
| 111 | 2020M07 | 3 |
| 112 | 2020M07 | 5 |
| 111 | 2021M07 | 10 |
| 112 | 2021M07 | 11 |
| 113 | 2021M07 | 2 |
Requested Output (in BOLD):
| RunYearMonth | YoY Same Store Growth |
| M07 | 162.5%* |
| M08 | .. |
* Only using ratio for customers 111+112 as they existed in the previous period. 113 did not exist (new customer).
So calculation is (10+11)/(3+5)-1 = 1.625
thanks guys
3 Replies
- amitchandak
Super User
Anonymous , move year to different table say date (This will work when the year is in the different table only
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ]) - daxer-almighty
Solution Sage
Such calculations are easy on condition that your model is correct, which means "built according to Best Practices" (meaning: star-schema). I gather from the picture you sent it's not the case. I'd be glad to help you but you have to have a correct model. Then everyhing becomes EASY. Here's where you could start exploring the world of correct models: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
