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.

 

UniqueIDDateDayWhat I want
11/1/20202
11/3/20202
11/8/20201
12/14/20201
21/1/20201
22/1/20201

 

I tried the follwing but was unsuccesful. How should I be handling the second part of the filter to get be the correct timeframe?

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

 

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
     )
)

1 Reply

  • 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
     )
)