Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count distinct based on two dates

Hi,

 

How to write the below condtion in DAX.

 

case when
(date1 - Date2) > 7
then count distinct (Account Num)
else
0


I have written as below


Count AcctNumber=
Var Acc_cond = (FORMAT ( TODAY (), "YYYY-MM-DD" ) - FORMAT ( "Account"[Start Date], "YYYY-MM-DD" ))

Var CountDist =
CALCULATE(DISTINCTCOUNT('Account'[Account Number]),
Acc_cond > 7 )
RETURN if( Cond1> 7, CountDist, 0)

unalbe to get the data, getting below error


Thanks,

 

 

  • Anonymous , Try like

     

    Count AcctNumber=
    Var Acc_cond = (FORMAT ( TODAY (), "YYYY-MM-DD" ) - FORMAT ( "Account"[Start Date], "YYYY-MM-DD" ))

    Var CountDist =
    CALCULATE(DISTINCTCOUNT('Account'[Account Number]),Filter(Account, datediff(Account[Start Date],TODAY (),day)+1 >7))
    RETURN CountDist

1 Reply

  • Anonymous , Try like

     

    Count AcctNumber=
    Var Acc_cond = (FORMAT ( TODAY (), "YYYY-MM-DD" ) - FORMAT ( "Account"[Start Date], "YYYY-MM-DD" ))

    Var CountDist =
    CALCULATE(DISTINCTCOUNT('Account'[Account Number]),Filter(Account, datediff(Account[Start Date],TODAY (),day)+1 >7))
    RETURN CountDist