March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 particular date.
This is very easy to do in Excel as shown in the table below I have 5 agents listed with varied Start and End Dates. (31/12/9999 used to show that there is no end date confirmed)
And on another table I can run a simple COUNTIFS formula to work out how many of these agents were Active on a particular date as shown below.
The trouble I have is in Power BI I need to replicate this, and I need a Date Range that dynamically updates according to the Dates on the RAW data. For this I set myself up a CALENDARAUTO for these dates. But I am having trouble linking these two tables.
Therefore my questions are as follows.
Solved! Go to Solution.
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
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
Thank you so much. That did it 🙂
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
27 | |
25 | |
22 | |
16 |