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-distin...
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

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