Count of ID based on Event Start Time & Date

[mohittimpus]

I have two tables:

1. "ID Date"

2. "Table 1"

In "Table 1", there are four columns "Start Time", "End Time",  "Event" & "Key".

In ID Date table, there are three columns "ID", "Date" & "Key" 

Both table are connect via "Key" column.

Now what I need is that distinct count of ID which lies in time interval greater than Start Time and within the same date of the Start time. I have created below DAX but it is not filtered within the same date of Start Time:

Measure = CALCULATE(DISTINCTCOUNT('ID Date'[ID]),FILTER('ID Date','ID Date'[dpDT]>min(Table1[Start Time])))

But I also want to filter it for only the same date.

Please find the dropbox link for the file below:

https://www.dropbox.com/s/vrjljhwklh7djlt/Demo%2024%20Jul.pbix?dl=0

[Mariusz]

Hi @mohittimpus 

You can try the below.

I think you are missing "=" operator and RELATED works probably better here.

Measure = 
CALCULATE(
    DISTINCTCOUNT('ID Date'[ID]),
    FILTER('ID Date','ID Date'[Date] >= RELATED( Table1[Start Time] ) )
) 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[mohittimpus]

I want only event start time date to be considered here, not other dates to include only 24 June.

[Mariusz]

Hi @mohittimpus 

Please can you describe your requirement in more detail?

Many Thanks 

Mariusz

[mohittimpus]

The result should be 3610, 

The event time starts from 24-06-2019 18:00:05, now what I need is the distinct count of ID from 24-06-2019 18:00:05 to 24-06-2019 23:59:59, it should not include a count for next date i.e. 25 June only till 24 June.