Forum Discussion
Solved
Count Active Agents Between Two Dates
Hi there, I am working on Power BI to create a report on Agent Staffing at my work. The formula I am trying to wrap my head around is to calculate the total number of Active agents on a particula...
Hi david148
no need to have the 2nd table
First create a date table
Date = CALENDAR ( MIN ( Table[Start Date] ), MAX ( Table[End Date] ) )Active = VAR SelectedDate = MAX ( 'Date'[Date] ) RETURN CALCULATE ( SUMX ( Table, IF ( Table[Start Date] <= SelectedDate && Table[End Date] >= SelectedDate, 1, 0 ) ), 'Date'[Date] <= SelectedDate )then create a measure
tamerj1
Community Champion
Community ChampionHi david148
no need to have the 2nd table
First create a date table
Date =
CALENDAR ( MIN ( Table[Start Date] ), MAX ( Table[End Date] ) )Active =
VAR SelectedDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
SUMX (
Table,
IF (
Table[Start Date] <= SelectedDate
&& Table[End Date] >= SelectedDate,
1,
0
)
),
'Date'[Date] <= SelectedDate
)then create a measure
