Calculation in a row from another (next) row.

Anonymous — Wed, 28 Oct 2020 13:22:00 GMT

Hi Guys,
I could use some help with a DAX Formula!

See the table below:

ContractNumber	PropertyNumber	StartDate	Enddate	VacancyReason	Fraud
Cr1	1	1-1-19	1-6-19	null	Yes
Cr2	1	2-6-19	4-6-19	NoTenant	No
Cr3	1	5-6-19	10-8-19	null	Yes
Cr4	1	11-8-19	22-9-19	NoTenant	No
Cr5	1	23-9-19	null	null	No
Cr6	2	3-1-19	6-6-19	null	No
Cr7	2	7-6-19	null	NoTenant	No

In the table above there is an example of contracts per property (1 property can have multiple contracts). If a tenant has committed fraud, the contract will be terminated (see Cr1 or Cr3) and an end date will be added to the data. Till a new tenant has rented the property a temporary contract will be created (see Cr2). When a new tenant has rented a property a new contract will be created (see Cr3 or Cr5: end date is null because the tenant is still renting the property).

Like you see in Cr1 & Cr3, a property can have multiple fraud cases, so to calculate the vacancy days the dax formula should look to the next vacancy contract of the property:
the vacancy contract startdate > end date of the fraud contract and the vacancy contract enddate < start date of the new contract

How can I calculate the vacancy days of a property after the contract has been terminated because of fraud? So in this case:

ContractNumber	PropertyNumber	StartDate	Enddate	VacancyReason	Fraud	VacancyDaysFraud
Cr1	1	1-1-19	1-6-19	null	Yes	2
Cr2	1	2-6-19	4-6-19	NoTenant	No	null (or 2)
Cr3	1	5-6-19	10-8-19	null	Yes	42
Cr4	1	11-8-19	22-9-19	NoTenant	No	null (or 42)
Cr5	1	23-9-19	null	null	No	null
Cr6	2	3-1-19	6-6-19	null	No	null
Cr7	2	7-6-19	null	NoTenant	No	null

Thanks

Re: Calculation in a row from another (next) row.

lbendlin — Tue, 03 Nov 2020 02:05:01 GMT

1. generally you cannot calculate something from the "next" row. You can calculate something from the "previous" row. Change your sort order.

2. speaking of sort order - you need some sort of an index column that makes sorting unambiguous. Your contract start date might be an option.

It is still not clear to me what the actual expected outcome is.

topic Re: Calculation in a row from another (next) row. in DAX Commands and Tips

Calculation in a row from another (next) row.

Re: Calculation in a row from another (next) row.