Count unique records in table by 3 calendar conditions

[DimaMD]

We have a table which includes 2 collums with dates, collum with ID and collum with owner. We need to count how many unique records are in the table on each owner, which considers our condition. 

Example of the table:

Date1	Owner	ID	Date2
16.06.2021	Vika	1712	30.06.2021
25.02.2021	Vika	1513	30.06.2021
25.02.2021	Vika	1608	30.06.2021
25.05.2021	Vika	1632	29.06.2021
25.05.2021	Vika	1648	30.06.2021
15.04.2021	Taras	1647	18.06.2021
31.05.2021	Stepan	1655	01.06.2021
16.06.2021	Julia	1213	25.06.2021
15.04.2021	Julia	1412	16.07.2021
25.03.2021	Julia	1515	30.07.2021
21.05.2021	Julia	1640	25.06.2021

The condition is:

date 1 < min calendar date

Date 2 >= min calendar date

Date 2<= max calemdar date

So for example. if we will put calendar dates like 01.06.2021-30.06.2021.

We need to count how many records meet our conditions. So in result we must receive:

Vika - 4

Taras - 1 

Stepan - 1 

Julia - 1

Please help us to solve this task. Thank you in advance!

[VahidDM]

Hi @DimaMD 

Try this measure:

Count = 
Var _Min=MIN('Date'[Date])
Var _Max=MAX('Date'[Date])
return
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Date1] < _Min,
    'Table'[Date2] >= _Min&&'Table'[Date2] <= _Max
    )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[DimaMD]

Sorry @VahidDM , but this measure does not work, result is blank😥
I can't understand why your measure worked

Relationship between table and calendar Date1

[VahidDM · Accepted solution]

Hi @DimaMD 

Try this one:

Count = 
VAR _Min =
    MIN ( 'Date'[Date] )
VAR _Max =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Owner] ),
        'Table'[Date1] < _Min
            && 'Table'[Date2] >= _Min
            && 'Table'[Date2] <= _Max
    )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[DimaMD]

Hi, @VahidDM 
Thank you, the measure worked

[PaulOlding]

Hi,

This measure gets your example result.  

Note, it uses DAX syntax introduced in September 2021 Desktop, so you'll need to be using that version.

Count = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Date1] < MIN('Date'[Date]),
    'Table'[Date2] >= MIN('Date'[Date]),
    'Table'[Date2] <= MAX('Date'[Date])
    )

[DimaMD]

Sorry, but this measure does not work, result is blank