topic Re: DAX formule for measure to know if there is a next record in the same table in DAX Commands and Tips

DAX formule for measure to know if there is a next record in the same table

admin-unidoze — Tue, 05 Mar 2024 14:42:20 GMT

I have a dataset with Payments for a specific yearly subscription:

ID, Customer, StartDate, EndDate, PaymentDate

When you renew your subscription, a new record is created in that Payments table, with the StartDate = EndDate previous + 1 and EndDate = StartDate + 12 months.

Example:

ID 1: Customer X: 15/02/2023 - 14/02/2024, Paid: 15/02/2023

ID 2: Customer X: 15/02/2024 - 14/02/2025, Paid: 09/02/2024

I want to create a measure in this dataset that indicates that there is a follow-up subscription.

So for ID 1:
ID 1: Customer X: 15/02/2023 - 14/02/2024, Paid: 15/02/2023 => Yes, because there is a record ID 2 for the same customer and with StartDate equals to the EndDate of record with ID 1 + 1 day.

ID 2: Customer X: 15/02/2024 - 14/02/2025, Paid: 09/02/2024 => No, because there is no record for the same customer and with StartDate equals to the EndDate of record with ID 1 + 1 day.

But I really haven't a single idea on how to start on this ...

Re: DAX formule for measure to know if there is a next record in the same table

JamesFR06 — Tue, 05 Mar 2024 15:19:20 GMT

A result with a new column in the table

Test =

Var DepDate=Cust[EndDate]+1

var customer=Cust[Customer]

var result=

if(COUNTROWS(filter(Cust,Cust[Customer]=customer&&Cust[StartDate]=DepDate))=1,"Yes","No")

return

result

Re: DAX formule for measure to know if there is a next record in the same table

admin-unidoze — Wed, 06 Mar 2024 15:56:39 GMT

Wauw thanks! This is absolutely the solution 🙂

Also the first time I work with inline variables in DAX so a good start for me!