Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count distinct rows between two dates

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:

 

EventDue Date
Event 115/06/2022
Event 230/06/2022
Event 305/07/2022
Event 415/10/2022
Event 520/11/2022
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello

    Try to use countrows instad of distinctcount

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    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