Don't count before starting date

[lost_flower]

Hello,

I try to count my lost customers and my visual looks like

where Lost = IF([Netamount_mon])=BLANK(),1,0) and year and month come from a date table.

Since customer A first purchased date is Oct 2021, it should not count as lost.

I tried IF('Date'[Date] < [first purchased date], BLANK(),...), but the date table does not work with IF function.

Happy for any idea.

Thanks a lot

[andhiii079845 · Accepted solution]

Works fine in may case: (right the table via dimDate)

Can you post your data model please? 

[lost_flower]

aaaahhhh, my relationship was not correct. Thank so much. Now it works. 😇😇😇😇😇😇😇😇😇

[lost_flower]

Thanks for your help. When I try your DAX I receive the same error 😞

My Date comes from a different Date Table. This is exactly where I struggle

I should mention that I tried it as a measure

[andhiii079845]

You have to use the by VAR firstime the date column from "6_lost"
VAR currenttime you have to use the date column from "6_lost"

[andhiii079845]

My solution: Tabelle2

Via DAX, your old value:

Lost = IF(sum(Tabelle2[Amount])>0,0,1)

New DAX:

Lost_new = 
 VAR firsttime = CALCULATE(min(Tabelle2[Date]),FILTER(ALL(Tabelle2),Tabelle2[Amount]>0))
 VAR currenttime = MAXX(Tabelle2,Tabelle2[Date])
VAR varnew = IF(sum(Tabelle2[Amount])= 0 && currenttime>firsttime,1,0)
RETURN varnew

Result: