Forum Discussion
Anonymous
4 years agoNot applicable
Recursively calculate diff between dates for customer contracts
Here is a dataset of customer contracts where a customer can have more than one contract, typically yearly. For example, Bruno Lage has 2 one year contracts, one starting on 05/02/20 and the other on...
- 4 years ago
Hi Anonymous
Here is the sample file with solution https://www.dropbox.com/t/pbCv6QBPPQp0LhAy
The table looks like this
You need to create an order column:Order = VAR CurrentID = Contracts[Id] VAR CurrentStartDate = Contracts[StartDate] VAR CurrentIdTable = FILTER ( Contracts, Contracts[Id] = CurrentID ) VAR Result = RANKX ( CurrentIdTable, Contracts[StartDate], , ASC ) RETURN ResultThen the difference column
Gap (days) = VAR PreviuosEndDate = LOOKUPVALUE ( Contracts[EndDate], Contracts[Id], Contracts[Id], Contracts[Order], Contracts[Order] - 1 ) VAR Difference = DATEDIFF ( PreviuosEndDate, Contracts[StartDate], DAY ) VAR Result = IF ( ISBLANK ( PreviuosEndDate ), 0, Difference ) RETURN ResultPlease let me know if this satistfies your requirement. Thank you
Whitewater100
Solution Sage
4 years agoHello:
I'm not sure I totally understand but I beleive this will get you close -you can change the IF Statement:
Three Calc Columns. Please see below. I hope this gets you going in the right direction!
Max EDate =
MAXX(
FILTER(
'Table',
'Table'[ID] = EARLIER('Table'[ID])),
'Table'[End Date])
Min Date =
MINX(
FILTER(
'Table',
'Table'[ID] = EARLIER('Table'[ID])),
'Table'[Start Date])
Answer = IF('Table'[END Date] < TODAY() -365,0,
INT('Table'[Max EDate] - 'Table'[Min Date] ))