Forum Discussion
If function with distinct count
Hi, I have a database as below and need to populate my client status as new or old. A client is considered as new if the 'date' and 'live date' are same. For rows 2 and 3, client is same however ...
assuming the deals are done in chronogical order you could then rephrase the logic to
for a given customer, if the Table1[date] is equal to the the Table1[Live_date] of the first deal made for that customer then new
which corresponds to this code
Status =
VAR CurrentCustomer = Table1[Client ID]
VAR CurrentDate = Table1[Live_date]
VAR FirstDealDate =
CALCULATE (
MIN ( Table1[Live_date] ),
FILTER ( Table1, Table1[Client ID] = CurrentCustomer )
)
RETURN
IF ( Table1[date] = FirstDealDate, "new", "old" )which gives your expected output
assuming the deals are done in chronogical order you could then rephrase the logic to
for a given customer, if the Table1[date] is equal to the the Table1[Live_date] of the first deal made for that customer then new
which corresponds to this code
Status =
VAR CurrentCustomer = Table1[Client ID]
VAR CurrentDate = Table1[Live_date]
VAR FirstDealDate =
CALCULATE (
MIN ( Table1[Live_date] ),
FILTER ( Table1, Table1[Client ID] = CurrentCustomer )
)
RETURN
IF ( Table1[date] = FirstDealDate, "new", "old" )
which gives your expected output