Hi All,
I'm trying to breakdown the variance between two dates (with dynamic date entry in a slicer) such that a user could understand what the variance drivers are (e.g., New customers, lost customers, cross-sold products, increase in users, increase in prices, etc.). Am being challenged by the dynamic date entry in the slicer and that I can't pass that type of information into a calculated column.
The source data is a table of contracts for subscription revenue, which can be simplified into:
- Customer ID
- Product ID
- Annual Recurring Revenue (ARR)
- Quantity
- Start Date
- End Date
I have a date slicer and calculated measures which allow the user to aggregate the Current ARR based on the date inputs (very similar formula is used for Starting ARR):
Current_ARR =
var _selecteddate = MAX('Dates'[Date])
return
CALCULATE(SUM(Table[Annual Recurring Revenue]),Table[Start Date] <= _selecteddate, Table[End Date] >= _selecteddate)
Works great.
I would like to be able to create analytics on the variance between two dates. For example, I want to aggregate and slice to understand customers who have added a new product; historically I would have calculated this in Excel by having a table at the customer/product level with the Current_ARR and Starting_ARR logic in it, plus a field for aggregated ARR at the customer level:
= If ( and (Current_ARR >0, Starting_ARR <= 0, Starting_ARR_Customer > 0), Current_ARR - Starting_ARR, 0)
Similarly understanding customers who cancelled a product:
= If ( and (Current_ARR < 0, Starting_ARR >= 0, Current_ARR_Customer >0), Current_ARR - Starting_ARR, 0)
Or maybe they are a totally new customer:
= If ( and (Current_ARR_Customer >0, Starting_ARR_Customer <=0), Current_ARR - Starting_ARR,0)
I could build the same logic into calculated columns in Power BI, but I lose the dynamic date entry that is so powerful (will just be static). Creating a table that includes every single date option feels overwhelming given the size of the data set.
Any advice on how to do this with measures? Or other alternatives?
