DAX help - Less than or Blank

[Anonymous]

Hi geniuss!!!

I'm trying to create a measure that I'll be using against a calendar table to plot number of active contacts at any given time, but I'm tearing my hair our with some of these measures...

In simple terms, I'm trying to create a measure, that will count the number of our members, at any given time, who have a qualification date of greater than or qual to *Date*

AND a leave date that is blank OR less than or equal to *Date*.

Its the 2nd part that I'm having trouble with.  So far I've got to:

COUNT('CE vwContact (2)'[Rics_contactno], 'CE vwContact (2)'[Rics_LapsedDate]<= 'BI vwCalendar (2)','-Calendar'[Date],

OR('CE vwContact (2)'[Rics_LapsedDate]= BLANK())

But its saying its incorrect.  I think it could be because I'm trying to do 2 formulas at the same time, but I've tried to seperate them with no luck either....

My whole formula is:

Members TEST =

CALCULATE(

DISTINCTCOUNT('CE vwContact (2)'[Rics_contactno]),

'CE vwContact (2)'[Rics_ElectionDate]>= 'BI vwCalendar (2)','-Calendar'[Date],

COUNT('CE vwContact (2)'[Rics_contactno], 'CE vwContact (2)'[Rics_LapsedDate]<= 'BI vwCalendar (2)','-Calendar'[Date],

'CE vwContact (2)', 'CE vwContact (2)'[Rics_LapsedDate]= BLANK()

))

But its just the counting of the blank or less than date.

Can anyone help?

Cheers

[Anonymous]

Awesome! That works, I'm going to have to play around with getting the visualisation of active members by date but the measure works,  I just had to change the Or to an AND, but its accepted in which is great.  

Thank you!!

[tamerj1 · Accepted solution]

Hi @Anonymous 
You may try

Members TEST =
VAR CurrentDate =
    MAX ( '-Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'CE vwContact (2)'[Rics_contactno] ),
        'CE vwContact (2)'[Rics_ElectionDate] >= CurrentDate,
        OR (
            'CE vwContact (2)'[Rics_LapsedDate] <= CurrentDate,
            'CE vwContact (2)'[Rics_LapsedDate] = BLANK ()
        )
    )