Calculate Time to reach First $25000

[mansi64011]

Hello,

I am trying to calculate the time when a customer hits hits $25000 mark on his account.

I have the following fields:

1. Customer ID:- Uniquely defining the customer

2. Date Account Opened:- the date when the account was opened

2. Invoice date : Dates when customer places order

4. Price: Amount charged to the customer for that order

Table looks like below:

Customer ID	Date Account Opened	Invoice Date	Price
111	8/3/2017	9/4/2017	$            3,424
627	3/5/2018	4/4/2018	$              10,000
111	8/3/2017	5/2/2018	$              15,892
339	6/9/2017	8/10/2017	$              17,029
339	6/9/2017	2/4/2019	$                 8,712

How should I do this in DAX?

[Anonymous · Accepted solution]

-- base measure
[Total] = SUM ( T[Price] )

[Target Hit Date] =
// This target value could be taken from a slicer
// instead of hard-coding it to allow maximum
// flexibility.
var __target = 25000
var __cummulativeTotals =
	ADDCOLUMNS (
		VALUES ( T[Invoice Date] ),
		"CumulTotal",
			var __invDate = T[Invoice Date]
			var __cumulativeTotal =
				calculate (
					[Total],
					T[Invoice Date] <= __invDate
				)
			return
				__cummulativeTotal
	)
var __hitDate =
minx (
	filter(
		__cummulativeTotals,
		[CumulTotal] >= __target
	),
	T[Invoice Date]
)
return
	__hitDate

If your model has only this one table and does not have a proper Date table connected to it... then you should CHANGE THE MODEL and make it correct. If you don't want to suffer later on... but it's your call.

Of course, the above measure works for any set of customers, not only for one.

Best

Darek

[Anonymous · Accepted solution]

-- base measure
[Total] = SUM ( T[Price] )

[Target Hit Date] =
// This target value could be taken from a slicer
// instead of hard-coding it to allow maximum
// flexibility.
var __target = 25000
var __cummulativeTotals =
	ADDCOLUMNS (
		VALUES ( T[Invoice Date] ),
		"CumulTotal",
			var __invDate = T[Invoice Date]
			var __cumulativeTotal =
				calculate (
					[Total],
					T[Invoice Date] <= __invDate
				)
			return
				__cummulativeTotal
	)
var __hitDate =
minx (
	filter(
		__cummulativeTotals,
		[CumulTotal] >= __target
	),
	T[Invoice Date]
)
return
	__hitDate

If your model has only this one table and does not have a proper Date table connected to it... then you should CHANGE THE MODEL and make it correct. If you don't want to suffer later on... but it's your call.

Of course, the above measure works for any set of customers, not only for one.

Best

Darek

[mansi64011]

Hello Derek,

Thank you for your fast response.
I tried to run this but I am getting an error that __cummulativeTotals is not a valid table, variable or funtion name ( after the 'return' statement).

I do have a Date table in my model connected to this table.

Thanks,

Mansi

[Anonymous · Accepted solution]

var __cummulativeTotals =  -- this one has an "s" on the end...
	ADDCOLUMNS (
		VALUES ( T[Invoice Date] ),
		"CumulTotal",
			var __invDate = T[Invoice Date]
			var __cummulativeTotal = -- this one does not... and it's had one "m" before
				calculate (
					[Total],
					T[Invoice Date] <= __invDate
				)
			return
				__cummulativeTotal -- and this one does not...
	)

Best

Darek