I am attempting to create a measure that calculates year-over-year growth with detailed categorization on the nature of the growth/decline. Here are the four categories that I need to capture at the store/unit level:
- New Unit - Units in their first twelve months of existence (i.e., operational in current period but not in prior year period)
- New Install - Units @ Month 13 (i.e., one-year anniversary of first operational period); these are separated b/c the first month generates install revenues which are higher than other months
- Same-Store Sales - Units operational in both current period and prior year period
- Churn - Units operational in prior year period but not operational in current year period
I have made several attempts at this and have figured out an interim solution that is very cumbersome. The issue within the PBI environment appears to be periods where there are no sales in current period but there ARE sales in prior period. I don’t know how to assign those a category. My attempts in PBI have included creating calculated columns which look at the current period relative to the prior period both relative to the first and last operational month. Again, for units that have churned, I am not able to pick up any of the churn months (i.e., twelve months after churn). Same story for units with zero sales in current month and positive sales in prior year month.
The way that I have solved for this is manipulating the Sales Data in Power Query. Essentially pivoting the data to show all months in the headers and then replacing all null values with zero and then unpivoting. From there, I am able to calculate all non-zero YoY changes and use logic to assign to a bucket. As you might imagine this creates a ton of data (esp w/ 25k units) and takes a long time to load
I can’t help but feel that I am missing something more basic. Do you guys have any thoughts?