Time to Value Calculation

Power BI
1

I’d like to measure how long (in days) it takes each customer to reach various revenue thresholds ($1k / $5k / $10k) throughout their lifetime with us. Anyone have general tips on how to approach this?

High level thoughts now are:

  1. make a variable with the start date: MIN of Order Date.

  2. make a variable that calculates the minimum date where the lifetime sales are above/equal to the target, using CALCULATE and FILTER.

  3. use DATEDIFF to get the time between 1 and 2.

I’m struggling with how to execute #2. Assuming my data model is perfect (it’s not :slight_smile: ), how would you tackle this?

1 Like
2

Hi @Jaclbark,
Thank you very much for posting your question in the forum.
I share a possible solution to the problem you have raised.

  1. We create the table Sales Thresholds

image

  1. Let’s create the following measures for this example:
Total Sales =
SUMX( Sales, Sales[Quantity] * RELATED( Products[Current Price] ))

Acc Total Sales = CALCULATE( [Total Sales],
FILTER( ALL( Dates[Date] ), Dates[Date] <= MAX( Dates[Date] ) )
)

Time to Value =
VAR _Thresholds = MAX( 'Sales Thresholds'[Value] )
VAR _FirstDatewithSales = FIRSTNONBLANK( ALL( Dates[Date] ), [Total Sales] )
VAR _VirtualTable = FILTER(
     ADDCOLUMNS(
         CROSSJOIN(
             VALUES( Customers[Customer Name] ),
                 ALL( Dates[Date] )
         ),
         "@Acc Sales", [Acc Total Sales]
     ),
     [@Acc Sales] >= _Thresholds
)
VAR _MinDateThresholds = MINX( _VirtualTable, [Date] )
VAR _Result = DATEDIFF( _FirstDatewithSales, _MinDateThresholds, DAY )

RETURN
_Result

image
image891×504 14.7 KB

Regards,

TimetoValue_JAFP.pbix (499.2 KB)

2 Likes
3

Thank you so much @jafernandezpuga! This is very helpful and I really appreciate you taking the time to lay it out so clearly.

I’ll give this a try soon and follow up with results.

1 Like