Datediff to get a cumulative value

[PDRTXRA]

Hello.
I have two tables. 
Table A: Clients table, each with an unique ID.
Table B: Consumptions table, each consumption has an unique ID and belongs to one clientID. Tables are related via clientID.

Lets say Table B has the following structure:
ID | ClientID | Date | Value
1 |  100 |  01/07/2022 | 5€
2 | 100  |  01/08/2022 | 6€

I need to create a measure that tells me the average time it takes for each client to reach 11€ of value.
So, for client 100 would be 1 month (31 days). 


Any help?

[v-zhangti · Accepted solution]

Hi, @PDRTXRA 

You can try the following methods.

Sample data:

Measure:

Running sum = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[ClientID]),[Date]<=SELECTEDVALUE('Table'[Date])))

Measure = 
Var _mindate=CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[ClientID]))
Var _first11=CALCULATE(MIN('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[ClientID]),[Running sum]=11))
Return
DATEDIFF(_mindate,_first11,DAY)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hashtag_pete]

Hi, 

and is the value 11 because this is the sum of 5+6 or do you want to get the time difference whenever the sum of value equals 11 vor other customers as well?

In other words: Is the sum of the column Value driving the time difference? If so, how do you connect the first time they reached 11 from the second time they might reach this value?

best

hashtag_pete

[PDRTXRA]

I want the time difference whenever the sum of value equals 11 for the first time