Calculated Column - Counting Duplicates in a Month

[Nagin]

Hi Everyone,

I am trying to create a calculated column to flag the number of duplicates, where if the patient "[NHS_Number_Nat_Pseud]"

has called "[EMAS Call Connect Date]" more than 5 times within a month, I have created the following but to no success

EMAS Reattendance Month =

VAR Numberofcalls =

CALCULATE(

COUNT(EMAS[NHS_Number_Nat_Pseud]),FILTER

(ALL(EMAS),EMAS[NHS_Number_Nat_Pseud]= EARLIER(EMAS[NHS_Number_Nat_Pseud]) && month(EMAS[EMAS Call Connect Date])=

MONTH(EARLIER(EMAS[EMAS Call Connect Date])

&& YEAR(EMAS[EMAS Call Connect Date])=

YEAR(EARLIER(EMAS[EMAS Call Connect Date])

))))

RETURN

IF (Numberofcalls <=4 && NOT(ISBLANK(Numberofcalls)), " No", IF (ISBLANK(Numberofcalls),"", " Yes") )

Any help on this would be much appricated 

Thanks

Nagin

[tamerj1 · Accepted solution]

Hi @Nagin 

please try

EMAS Reattendance Month =
VAR CurrentMonth =
    MONTH ( EMAS[EMAS Call Connect Date] )
VAR CurrentYear =
    YEAR ( EMAS[EMAS Call Connect Date] )
VAR CurrentIDtable =
    CALCULATETABLE ( EMAS, ALLEXCEPT ( EMAS, EMAS[NHS_Number_Nat_Pseud] ) )
VAR FilteredTable =
    FILTER (
        CurrentIDtable,
        EMAS[NHS_Number_Nat_Pseud] <> BLANK ()
            && MONTH ( EMAS[NHS_Number_Nat_Pseud] ) = CurrentMonth
            && YEAR ( EMAS[NHS_Number_Nat_Pseud] ) = CurrentYear
    )
RETURN
    IF ( COUNTROWS ( FilteredTable ) > 5, "Yes", "No" )

[Nagin · Accepted solution]

Hi @tamerj1 

Thanks for this worked perfectly

[tamerj1 · Accepted solution]

Hi @Nagin 

please try

EMAS Reattendance Month =
VAR CurrentMonth =
    MONTH ( EMAS[EMAS Call Connect Date] )
VAR CurrentYear =
    YEAR ( EMAS[EMAS Call Connect Date] )
VAR CurrentIDtable =
    CALCULATETABLE ( EMAS, ALLEXCEPT ( EMAS, EMAS[NHS_Number_Nat_Pseud] ) )
VAR FilteredTable =
    FILTER (
        CurrentIDtable,
        EMAS[NHS_Number_Nat_Pseud] <> BLANK ()
            && MONTH ( EMAS[NHS_Number_Nat_Pseud] ) = CurrentMonth
            && YEAR ( EMAS[NHS_Number_Nat_Pseud] ) = CurrentYear
    )
RETURN
    IF ( COUNTROWS ( FilteredTable ) > 5, "Yes", "No" )

[Nagin · Accepted solution]

Hi @tamerj1 

Thanks for this worked perfectly

[tamerj1 · Accepted solution]

@Nagin 
Great to hear that!
Kindly conisder marking my reply as acceptable solution