DISTINCTCOUNT excluding certain records

[Kiki]

Hello. I need some help.

Below is an example of my model:

I am trying to get a count of all transactions with a First Transaction Date before 01/02/2022 (based on slicer selection) using:

CALCULATE(DISTINCTCOUNT('Transaction Table'[Rreference]),

FILTER('Transaction Table','Transaction Table'[First Transaction Date]<MIN('Date Table'[Date].[Date])

However, the above gives me a count of 2, instead of 3, omitting reference 167 because it doesn’t have a transaction date in the month of February 2022.

Any help will be much appreciated.

Thanks

[tamerj1 · Accepted solution]

Hi @Kiki 
Please try

=
CALCULATE (
    DISTINCTCOUNT ( 'Transaction Table'[Rreference] ),
    'Transaction Table'[First Transaction Date] < MIN ( 'Date Table'[Date] ),
    CROSSFILTER ( 'Transaction Table'[Transaction Date], 'Date Table'[Date], NONE )
)

[Kiki]

YES!! Thank you so much! It works!

[SpartaBI]

@tamerj1 my man 🙂

[tamerj1]

☺️

[SpartaBI]

@Kiki please try:

CALCULATE(DISTINCTCOUNT('Transaction Table'[Rreference]),

'Transaction Table'[First Transaction Date]<MIN('Date Table'[Date].[Date])
)

      

Showcase Report – Contoso By SpartaBI

[Kiki]

Hi. This is not working unfortunately.

[SpartaBI]

@Kiki ok, no worries 🙂 will get there. 
Can you maybe share the file / sample of the file with fake data and explain there in a text box the desired output you want to get.

[Kiki]

Hi, 

I hope this works?

Transaction Table
Reference	Transaction Date	First Transaction Date	Last Date
123	01/01/2019	01/01/2019	01/03/2022
123	01/05/2020	01/01/2019	01/03/2022
123	01/02/2022	01/01/2019	01/03/2022
145	01/02/2021	01/02/2021	01/03/2022
145	01/03/2022	01/02/2021	01/03/2022
167	01/03/2018	01/03/2018
167	01/01/2022	01/03/2018
187	01/01/2017	01/01/2017
187	15/02/2022	01/01/2017

CALCULATE(DISTINCTCOUNT('Transaction Table'[Rreference]),
FILTER('Transaction Table','Transaction Table'[First Transaction Date]<MIN('Date Table'[Date].[Date])
&&OR('Transaction Table'[Last Transaction Date]>=MIN('Date Table'[Date].[Date]),ISBLANK('Transaction Table'[Last Transaction Date])))

I am expecting the count to be 4 i.e. 123, 145,167 & 187 because all of these references have a First Transaction Date of before the 1st of February 2022, and a Last Transaction Date of either after the 1st of February 2022 or blank.

However, I am getting a count of 2 i.e. 123 & 187, and I think this because these references have a Transaction Date within the month of February 2022 i.e. the selected date in the slicer.