Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowI have absence data:
ID | Start | End | Duration | Days in Month | Working Days | Leave Entitlements | Days Available | Absence | Net |
727 | 02/01/2019 | 07/01/2019 | 6 | 31 | 23 | 2.5 | 20.5 | 6 | 14.5 |
727 | 10/03/2020 | 11/03/2020 | 2 | 31 | 22 | 2.5 | 19.5 | 2 | 17.5 |
1150 | 03/06/2019 | 07/08/2019 | 66 | 30 | 20 | 2.5 | 17.5 | 7 | -48.5 |
1150 | 03/06/2019 | 07/08/2019 | 66 | 30 | 20 | 2.5 | 17.5 | 31 | -48.5 |
1150 | 03/06/2019 | 07/08/2019 | 66 | 30 | 20 | 2.5 | 17.5 | 28 | -48.5 |
Where I'm subtracting [END] - [START] = Duration and then subtracting the ABSENCE from this.
However for periods longer than 1 month it groups the total (66 days) rather than individual days (7,31,28) is there a way to get it so the chart is like:
ID | Start | End | Duration | Days in Month | Working Days | Leave Entitlements | Days Available | Absence | Net |
727 | 02/01/2019 | 07/01/2019 | 6 | 31 | 23 | 2.5 | 20.5 | 6 | 14.5 |
727 | 10/03/2020 | 11/03/2020 | 2 | 31 | 22 | 2.5 | 19.5 | 2 | 17.5 |
1150 | 03/06/2019 | 07/08/2019 | 66 | 30 | 20 | 2.5 | 17.5 | 7 | 10.5 |
1150 | 03/06/2019 | 07/08/2019 | 66 | 30 | 20 | 2.5 | 17.5 | 31 | -13.5 |
1150 | 03/06/2019 | 07/08/2019 | 66 | 30 | 20 | 2.5 | 17.5 | 28 | -10.5 |
PBIX attached for better explanation .
https://www.dropbox.com/s/zjizlyun1nuur15/Sample%20Absence.pbix?dl=0
@Anonymous , Have tried
datediff( [START],[END] ,day)
for working day refer : https://www.sqlbi.com/articles/counting-working-days-in-dax/
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
24 | |
15 | |
13 | |
12 | |
8 |
User | Count |
---|---|
30 | |
22 | |
15 | |
14 | |
14 |