Count distinct rows between two dates

[shaunmag]

Hi all,

I need to create a simple measure that returns the number of rows where the value in a date column falls between two dates. I have had some success, but it only seems to return non-distinct values (for example, if two dates are the same it returns just '1' value). Any help would be greatly appreciated.

Specifically, the measure I want to create goes something like this:

Events in Next 90 Days = 

CALCULATE(

DISTINCTCOUNT('Table'[Event]),

DATESBETWEEN('Table'[Due Date],Today(), Today()+90)

))

Here is the table:

Event	Due Date
Event 1	15/06/2022
Event 2	30/06/2022
Event 3	05/07/2022
Event 4	15/10/2022
Event 5	20/11/2022

[Anonymous · Accepted solution]

Hi @shaunmag ,

Which part is wrong? According to your description, just change the distinctcount() to count() and it will be fine.

Events in Next 90 Days = 

CALCULATE(

COUNT('Table'[Event]),

DATESBETWEEN('Table'[Due Date],Today(), Today()+90)

))

Best Regards,

Jay

[shaunmag]

I've tried that, and literally everything else, but nothing is working sadly.

[Anonymous]

Hello

Try to use countrows instad of distinctcount

[shaunmag]

I did manage to get this working, but it only includes one filter: if the date is in the past. What I need is for this simple measure to include an additional time-based filter so that I can target all values that fall between two dates:

Risks Overdue =

CALCULATE(COUNT(Table[Event]),

FILTER(Table,Table[Event].[Date] < Today() ))

[Anonymous]

ok. Try this as measure

Event within 90days =

var mindate=today()

var maxdate=today()+90

var result=countrows(filter(summarize(Feuil7,Feuil7[Due Date]),Feuil7[Due Date]>=mindate&&Feuil7[Due Date]<=maxdate))

return

result