Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
@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 ])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |