'Same Store' Utilzation

[edayan]

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

[daxer-almighty]

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

[amitchandak]

@edayan , 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 ])

[edayan]

Hi @amitchandak ,

I'm getting an error with the last year one for some reason..

Any Clue?

Many thanks