Forum Discussion

arolon2's avatar
arolon2
Frequent Visitor
6 years ago
Solved

How to count an ID over a specific timeframe

I have a table (Roster) that includes IDs number and dates. What I am trying to do is count is the amount of occurances of that ID from the date listed the row until a week later.   UniqueID Da...
Need Help
  • az38's avatar
    az38
    6 years ago

    Hi arolon2 

    DATEADD() is a very tricky function https://docs.microsoft.com/en-us/dax/dateadd-function-dax

    The result table includes only dates that exist in the dates column.

      create a column

    IDCOUNT = 
VAR CurrentID = Roster[UniqueID]
VAR CurrentWeek = Roster[DateDay]
VAR NextWeek = Roster[DateDay] + 7
RETURN
COUNTROWS(
     FILTER(
     Roster,
     CurrentID = Roster[UniqueID] &&
     CurrentWeek <= Roster[DateDay] && Roster[DateDay] < NextWeek
     )
)
az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

Hi arolon2 

DATEADD() is a very tricky function https://docs.microsoft.com/en-us/dax/dateadd-function-dax

The result table includes only dates that exist in the dates column.

  create a column

IDCOUNT = 
VAR CurrentID = Roster[UniqueID]
VAR CurrentWeek = Roster[DateDay]
VAR NextWeek = Roster[DateDay] + 7
RETURN
COUNTROWS(
     FILTER(
     Roster,
     CurrentID = Roster[UniqueID] &&
     CurrentWeek <= Roster[DateDay] && Roster[DateDay] < NextWeek
     )
)