Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear experts,
I have a dataset like below. We wish to create a measure to calculate the Average Headcount of the period by Sumimg the not empty headcount of the erlist date and not empty headcount of the latest date's the user choose, and then divided by 2.
For example, if the user choose 6/3/2018 to 6/10/2018, the Average Headcount of the period= (5100+4500)/2.
If the user choose 6/6/2018 to 6/11/2018, the Average Headcount of the period = (5200+5000)/2
Date | Headcount |
6/1/2018 | 5000 |
6/2/2018 | 4500 |
6/3/2018 | |
6/4/2018 | |
6/5/2018 | 5100 |
6/6/2018 | 5200 |
6/7/2018 | 5400 |
6/8/2018 | 5200 |
6/9/2018 | 4900 |
6/10/2018 | 4500 |
6/11/2018 | 5000 |
6/12/2018 | 4700 |
How should we write the expression to achieve that?
Many thanks!
Best regards,
Tom
Solved! Go to Solution.
You can use this MEASURE
Measure = VAR MyMin = CALCULATE ( MIN ( [Headcount] ), CALCULATETABLE ( FIRSTDATE ( Table1[Date] ), Table1[Headcount] <> BLANK () ) ) VAR MyMax = CALCULATE ( MAX ( [Headcount] ), CALCULATETABLE ( LASTDATE ( Table1[Date] ), Table1[Headcount] <> BLANK () ) ) RETURN ( MyMin + MyMax ) / 2
You can use this MEASURE
Measure = VAR MyMin = CALCULATE ( MIN ( [Headcount] ), CALCULATETABLE ( FIRSTDATE ( Table1[Date] ), Table1[Headcount] <> BLANK () ) ) VAR MyMax = CALCULATE ( MAX ( [Headcount] ), CALCULATETABLE ( LASTDATE ( Table1[Date] ), Table1[Headcount] <> BLANK () ) ) RETURN ( MyMin + MyMax ) / 2
Hi Zubair, @Zubair_Muhammad
Just a further quetsion, what if the raw data set is like below. The database will refresh the active employee ID in following table once a day. The "Report Date" is the refresh date.
For example, on 6/1/2018 and 6/2/2018, they are empty which means there are no employees.
On 6/3/2018, there are 4 employees joined.
On 6/4/2018, there are 4 employeess (no one join or left).
On 6/5/2018, there are 3 employees (one left).
I sitll wish to create a to create a measure to calculate the Average Headcount of the period by Sumimg the not empty headcount of the erlist date and not empty headcount of the latest date's the user choose, and then divided by 2.
For example, if the user choose 6/1/2018 to 6/5/2018, the Average Headcount of the period= (4+3)/2.
If the user choose 6/3/2018 to 6/4/2018, the Average Headcount of the period = (4+4)/2
Report Date | Employee ID |
6/1/2018 | |
6/2/2018 | |
6/3/2018 | 111 |
6/3/2018 | 222 |
6/3/2018 | 333 |
6/3/2018 | 444 |
6/4/2018 | 111 |
6/4/2018 | 222 |
6/4/2018 | 333 |
6/4/2018 | 444 |
6/5/2018 | 111 |
6/5/2018 | 222 |
6/5/2018 | 333 |
How should we modify the Expression to achieve that?
Many thanks!
Best regards,
Tom
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |