Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone!
The task is to build a plan for recruiting group based on the entries in table. They open a vacancy by adding a new row and set estimated hire date under internal SLA and fill hire date when they close it. Hire date column remains empty till they sign a new employee.
Data sample:
Estimated hire date | Comments | Status | Hire date |
29.01.2021 | cancel | Hold | |
21.12.2020 | Fed | Offer accepted | 14.01.2021 |
30.12.2020 | Gri | Offer accepted | 13.01.2021 |
24.01.2021 | Dem | Offer accepted | 12.01.2021 |
27.01.2021 | Shev | Offer accepted | 21.01.2021 |
26.01.2021 | Pol | Offer accepted | 26.01.2021 |
28.01.2021 | Sel | Offer accepted | 23.01.2021 |
28.01.2021 | Ela | Offer accepted | 26.01.2021 |
29.01.2021 | Pil | Offer accepted | 01.02.2021 |
29.01.2021 | Sil | Offer accepted | 07.03.2021 |
29.01.2021 | Rom | Offer accepted | 27.01.2021 |
29.01.2021 | Sor | Offer accepted | 28.01.2021 |
10.02.2021 | Sim | Offer accepted | 25.03.2021 |
17.02.2021 | Sid | Offer accepted | 24.02.2021 |
09.02.2021 | Gav | Offer accepted | 28.01.2021 |
01.02.2021 | Gar | Offer accepted | 05.02.2021 |
01.02.2021 | Fedo | Offer accepted | 25.01.2021 |
09.02.2021 | Dru | Offer accepted | 07.02.2021 |
2 hard points for me here:
So I'm asking for help to build 2 measures for this total plan and counting overdue vacancies for each month.
All I have now is running total for rows:
= CALCULATE(COUNTROWS('Regions'),
FILTER(ALL('Calendar'),
'Calendar'[Date]<=MAX('Calendar'[Date])
))
I tried to add additional context for someting like 'Regions'[Estimated hire date] < 'Regions'[Hire date] but not succeeded.
Would be glad for your comments.
PBIX file with sample data: https://www.mediafire.com/file/q9lgrjp3eyb1t55/tst2.pbix/file
Solved! Go to Solution.
Got a solution. At first, we need to set 2 inactive relationships in model:
And then we have the measure:
RT =
CALCULATE( COUNTROWS('Regions'),
FILTER( VALUES('Regions'[Estimated hire date]), 'Regions'[Estimated hire date] <= MAX( 'Calendar'[Date] ) ),
FILTER( VALUES('Regions'[Hire date]),
OR('Regions'[Hire date] >= MIN( 'Calendar'[Date] ),
'Regions'[Hire date] = BLANK() ) )
)
'Calendar'[Date] should be put in visualization to build dates context:
Thus we already have 3 rows for March, they are for vacancies that were not closed but opened in Dec, Jan or Feb.
Got a solution. At first, we need to set 2 inactive relationships in model:
And then we have the measure:
RT =
CALCULATE( COUNTROWS('Regions'),
FILTER( VALUES('Regions'[Estimated hire date]), 'Regions'[Estimated hire date] <= MAX( 'Calendar'[Date] ) ),
FILTER( VALUES('Regions'[Hire date]),
OR('Regions'[Hire date] >= MIN( 'Calendar'[Date] ),
'Regions'[Hire date] = BLANK() ) )
)
'Calendar'[Date] should be put in visualization to build dates context:
Thus we already have 3 rows for March, they are for vacancies that were not closed but opened in Dec, Jan or Feb.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |