Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi, i'm trying to display the number of workers today, I have a start date and a leaving date, so i need to show today's active workers and then with a slicer I want to show the same information by month, hope someone can help me.
example data:
Worker ID | start date | leaving date |
19311196 | 2003-06-15 | 2018-01-24 |
11424431 | 2005-07-26 | 2018-01-28 |
10574322 | 2009-09-17 | 2018-01-31 |
10240660 | 2001-10-03 | |
18264874 | 2001-07-28 | |
17692937 | 2004-10-02 | |
12298585 | 2011-07-03 |
(date yyyy-mm-dd)
the ones with no leaving date, means that are still working on the company.
thank you
Solved! Go to Solution.
Hi @MarcoLopez,
Sample for your refernce, please check the following steps as below. If I misunderstood your request, kindly share your excepted result to me.
1. Crete a date table and one calculated column in it.
date = CALENDARAUTO()
YearMonth = YEAR('date'[Date]) &" " &FORMAT('date'[Date],"mmm")
2. Create two measures to get the result as we need.
today's active workers = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALL ( Table1 ), Table1[leaving date] = BLANK () ) )
by month = VAR mindate = CALCULATE ( MIN ( 'date'[Date] ), ALLSELECTED ( 'date'[YearMonth] ) ) RETURN CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, OR ( Table1[start date] <= mindate && Table1[leaving date] >= mindate, Table1[start date] <= mindate && ISBLANK ( Table1[leaving date] ) ) ) )
For more details, please check the pbix as attached.
Regards,
Frank
Hi @MarcoLopez,
Sample for your refernce, please check the following steps as below. If I misunderstood your request, kindly share your excepted result to me.
1. Crete a date table and one calculated column in it.
date = CALENDARAUTO()
YearMonth = YEAR('date'[Date]) &" " &FORMAT('date'[Date],"mmm")
2. Create two measures to get the result as we need.
today's active workers = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALL ( Table1 ), Table1[leaving date] = BLANK () ) )
by month = VAR mindate = CALCULATE ( MIN ( 'date'[Date] ), ALLSELECTED ( 'date'[YearMonth] ) ) RETURN CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, OR ( Table1[start date] <= mindate && Table1[leaving date] >= mindate, Table1[start date] <= mindate && ISBLANK ( Table1[leaving date] ) ) ) )
For more details, please check the pbix as attached.
Regards,
Frank
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |