DAX query for COUNT with WHERE condition

[Anonymous]

Hi, I have table with custid, createddate and cancelled date. I want to create a calcualted measure using DAX for couting number of custid's WHERE createdate is BETWEEN April1st - March31t financial year BUT EXCLUDE those custid's WHERE  cancelled date is BETWEEN April1st - March31t financial year.

Can someone please help me with that DAX query ? Many Thanks

[Anonymous]

Hi Amit, But I cannot see your query filtering for the below conditon for both created and cancelled date . Am i missing something ?

BETWEEN April1st - March31t

[Anonymous]

Hi Thanks Amit. Yes I have a seperate DATE table in my model and its got relation with both those columns. ACTIVE relation on the created date and non-active relation on the cancelled date.

Thanks

[amitchandak · Accepted solution]

@Anonymous , Assume you use a date table and FY is column in date table and it is selected in a slicer

If date table is independent and joined to any of these columns

measure =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = minx(ALLSELECTED('Date'),'Date'[Date])
return
calculate(count(Table[customer_id]), filter(Table, Table[createdate] >=Min && Table[createdate] <=Max && Table[cancelled date] >=Min && Table[cancelled date] <=Max ))

And if date table is joined to createdate
measure =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = minx(ALLSELECTED('Date'),'Date'[Date])
return
calculate(count(Table[customer_id]), filter(Table, Table[cancelled date] >=Min && Table[cancelled date] <=Max ))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.