Calculate distinct count only within 6 Months

[Anonymous]

Hello there

This is probably another question for help replacing the COUNTIFS formula with DAX.

I've tried a few similar solutions posted on here and even tried Grouping my Table etc but with no success.

What I want is to distinct count the PRPcode only within the last 6 months in the table below:

PRPcpde            JNLCODE        Date

CBL1210067      123                  01/01/2016

CBL1210067      1234                12/01/2015

CBL1210067      12345               05/01/2016

CBL1210067      11235               02/04/2015

I.e the answer should be 2

In addition I'm also having trouble working out the1/COUNTIFS DAX?? (no date range required)

I.e for the above table the value of each row should be 0.25

Many thanks in advance for your help!

MG

[Baskar · Accepted solution]

Hi MG

M = Calculate(DistinctCount(PRPcpde),filter(Date>= Dateadd(Today(),-6,Month))

or

M = Calculate(DistinctCount(PRPcpde),filter(Date>= Dateadd(max(date),-6,Month))

Try this

[v-sihou-msft · Accepted solution]

@Anonymous

In this scenario, if you want to distinct count the PRPcode only within the last 6 months, you can create measures with following formulas. To count the PRPcode instead of distinct count, you can replace “DISTINCTCOUNT” with “COUNTA” in following formulas.

DistinctCount_Of_PRPcode_Within_Last_6_Months(From Today) =

CALCULATE (

    DISTINCTCOUNT ( Table1[PRPcode] ),

    DATESINPERIOD(Table1[Date], TODAY(), -6, MONTH)

)

DistinctCount_Of_PRPcode_Within_Last_6_Months(From Latest Date in Table1) =

CALCULATE (

    DISTINCTCOUNT ( Table1[PRPcode] ),

    DATESINPERIOD(Table1[Date], LASTDATE(Table1[Date]), -6, MONTH)

)

For 1/COUNTIFS DAX (no date range required), you can also create a measure with following formula.

1/DistinCount_Of_PBRcode = 1 / DISTINCTCOUNT(Table1[PRPcode])

Regards,

[Anonymous]

Thanks to both of you!! Both worked for me! Cheers.

[Anonymous]

Hi 

I'm trying to duplicate the above measure as a new column. 

Reason for this is that I want users to See Records within the graph which you cannot currently do with a measure. 

I've tried various variations but never get the same figure as the measure. 

Any suggestions? 

[Baskar · Accepted solution]

Hi MG

M = Calculate(DistinctCount(PRPcpde),filter(Date>= Dateadd(Today(),-6,Month))

or

M = Calculate(DistinctCount(PRPcpde),filter(Date>= Dateadd(max(date),-6,Month))

Try this