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,
I will be so thankful if someone can assist writing smart DAX for the below scenario. I hope this will be a quick DAX coding for someone.
I have a table with 4 columns
Assignment number | Department | Sickness Start Date | Sickness End Date |
1 | IT | 04/04/2020 | 02/04/2020 |
2 | HR | 01/04/2020 | |
3 | Finance | 01/03/2020 | 05/04/2020 |
4 | Finance | 15/03/2020 | 06/04/2020 |
5 | HR | 10/02/2020 | |
6 | HR | 01/01/2020 | |
7 | IT | 02/01/2020 | 01/04/2020 |
8 | Nursing | 03/01/2020 | 02/04/2020 |
9 | IT | 04/01/2020 | |
10 | Nursing | 05/01/2020 | |
11 | Nursing | 06/01/2020 | 07/04/2020 |
12 | Nursing | 07/01/2020 | 06/04/2020 |
13 | Nursing | 08/01/2020 | 06/04/2020 |
14 | Nursing | 09/01/2020 |
I want to find out how many staff members were off sick on each day of April 2020 till 9th April. Answer will be like
Month day | Off sick | HR Off sick | IT Off sick | Nursing Off sick | Finance Off sick |
01 Apr 2020 | 14 | 3 | 3 | 6 | 2 |
02 Apr 2020 | 13 | 3 | 2 | 6 | 2 |
03 Apr 2020 | 11 | 3 | 1 | 5 | 2 |
04 Apr 2020 | 11 | 3 | 1 | 5 | 2 |
05 Apr 2020 | 11 | 3 | 1 | 5 | 2 |
06 Apr 2020 | 10 | 3 | 1 | 5 | 1 |
07 Apr 2020 | 7 | 3 | 1 | 3 | 0 |
08 Apr 2020 | 6 | 3 | 1 | 2 | 0 |
09 Apr 2020 | 6 | 3 | 1 | 2 | 0 |
I do not have table to keep each day of month separately but can create a DimDate but how to create a link between these two tables as it seems there is a complex linkage.
If someone copy this dataset in PBIX and share will be ideal.
Formula to calculate each day is, for example if you are check off sick on 4th April = All staff with Sick end date as blank or null and also those whose sick end date is on or after 5th April.
Please suggest how I can achieve this.
Many thanks
Solved! Go to Solution.
Hi @Anonymous ,
I just changed the code for D_Date table, creating the date with Apr 9th - 30.
I hope it works.
If this is a solution, please mark as a solution and kudos.
Ricardo
Hi @camargos88
This is outstanding. Thanks a lot for such a prompt and perfect solution.
One last things,
What if I do not want a slicer(date selected 01Apr-09Apr) and just want to bring last 30 days automatically instead of leaving users with slicer? Last 30 days from max date which is 09 Apr?
Already so helpful but if you can provide this, that will be great.
Your solution has given me a new direction in DAX scripting.
* correction in data: Off sick for 01Apr is 13 not 14 and @camargos88 solution has automatically fixed it, in case if someone use this example in future.
Many thanks
Hi @Anonymous ,
I just changed the code for D_Date table, creating the date with Apr 9th - 30.
I hope it works.
If this is a solution, please mark as a solution and kudos.
Ricardo
Awesome!!
I changed Date table as
For last 30 days from today 's date
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |