Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi to all and many thanks in advance!
I have this information.
I produce a weekly report. I want to calculate by the date of each report a) how many positions were still open by each report date, b) how many positions had closed by by each report date, c) what the average time to recruit (for all positions) was by each report date.
I do not know if it plays a role, but the report is every Thursday.
Position Title | Date position opened | Date position closed | Time to recruit |
a | 01/01/2022 | 20/01/2022 | 19 |
b | 15/01/2022 | 30/01/2022 | 15 |
c | 16/01/2022 | 18/01/2022 | 2 |
d | 14/03/2022 | 16/03/2022 | 2 |
e | 20/03/2022 | 05/05/2022 | 45 |
f | 25/03/2022 | 25/4/2022 | 30 |
g | 25/4/2022 | 01/5/2022 | 7 |
Solved! Go to Solution.
Hi, @KatBous
You can try the following methods.
Measure:
Count open =
VAR _N1 =
CALCULATE (
COUNT ( 'Table'[Position Title] ),
FILTER (
ALL ( 'Table' ),
[Date position opened] <= SELECTEDVALUE ( 'Report date'[Date] )
&& [Date position closed] >= SELECTEDVALUE ( 'Report date'[Date] )
&& [Date position closed] <> BLANK ()
)
)
VAR _N2 =
CALCULATE (
COUNT ( 'Table'[Position Title] ),
FILTER (
ALL ( 'Table' ),
[Date position closed] = BLANK ()
&& [Date position opened] <= SELECTEDVALUE ( 'Report date'[Date] )
)
)
RETURN
_N1 + _N2
Count closed =
CALCULATE (
COUNT ( 'Table'[Position Title] ),
FILTER (
ALL ( 'Table' ),
[Date position closed] <= SELECTEDVALUE ( 'Report date'[Date] )
&& [Date position closed] <> BLANK ()
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @KatBous
You can try the following methods.
Create a new date table and calculate Thursday.
Date = CALENDAR(MIN('Table'[Date position opened]),MAX('Table'[Date position closed]))
Column:
Weekday = WEEKDAY([Date],2)
Extract the date of each report.
New table:
Report date = FILTER('Date',[Weekday]=4)
Measure:
Count open =
CALCULATE (
COUNT ( 'Table'[Position Title] ),
FILTER (
ALL ( 'Table' ),
[Date position opened] <= SELECTEDVALUE ( 'Report date'[Date] )
&& [Date position closed] >= SELECTEDVALUE ( 'Report date'[Date] )
)
)
Count closed =
CALCULATE (
COUNT ( 'Table'[Position Title] ),
FILTER (
ALL ( 'Table' ),
[Date position closed] <= SELECTEDVALUE ( 'Report date'[Date] )
)
)
Average =
CALCULATE (
AVERAGE ( 'Table'[Time to recruit] ),
FILTER (
ALL ( 'Table' ),
[Date position closed] <= SELECTEDVALUE ( 'Report date'[Date] )
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much @v-zhangti for the immediate and detailed reply!!!
One info that I forgot to include in my example table (new one below) is that some rows have blank "Date position closed" and "Time to recruit", as some positions are still open when I draft the report. How should I change the measures?
And also, using the measures, the table in PowerBi stops one week before the last week. It stops in 10 November while the last report date is 17 November.
Position Title | Date position opened | Date position closed | Time to recruit |
a | 18/10/2022 | 19/10/2022 | 2,00 |
b | 18/10/2022 | 01/11/2022 | 15,00 |
c | 20/10/2022 | ||
d | 25/10/2022 | ||
e | 25/10/2022 | ||
f | 25/10/2022 | ||
g | 25/10/2022 | ||
h | 04/11/2022 | 14/11/2022 | 11,00 |
i | 08/11/2022 |
Hi, @KatBous
You can try the following methods.
Measure:
Count open =
VAR _N1 =
CALCULATE (
COUNT ( 'Table'[Position Title] ),
FILTER (
ALL ( 'Table' ),
[Date position opened] <= SELECTEDVALUE ( 'Report date'[Date] )
&& [Date position closed] >= SELECTEDVALUE ( 'Report date'[Date] )
&& [Date position closed] <> BLANK ()
)
)
VAR _N2 =
CALCULATE (
COUNT ( 'Table'[Position Title] ),
FILTER (
ALL ( 'Table' ),
[Date position closed] = BLANK ()
&& [Date position opened] <= SELECTEDVALUE ( 'Report date'[Date] )
)
)
RETURN
_N1 + _N2
Count closed =
CALCULATE (
COUNT ( 'Table'[Position Title] ),
FILTER (
ALL ( 'Table' ),
[Date position closed] <= SELECTEDVALUE ( 'Report date'[Date] )
&& [Date position closed] <> BLANK ()
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
106 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
158 | |
124 | |
76 | |
74 | |
63 |