Calculation of measure on the fly per day count of sick staff

[Anonymous]

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

[camargos88 · Accepted solution]

Hi @Anonymous ,

I've create this file as an example: Download PBIX 

Ricardo

[camargos88 · Accepted solution]

Hi @Anonymous ,

I've create this file as an example: Download PBIX 

Ricardo

[Anonymous]

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

[camargos88 · Accepted solution]

Hi @Anonymous ,

I just changed the code for D_Date table, creating the date with Apr 9th - 30.

Download PBIX 

I hope it works.

If this is a solution, please mark as a solution and kudos.

Ricardo

[Anonymous]

Awesome!!

I changed Date table as 

For last 30 days from today 's date

D_Date2 = CALENDAR(TODAY()-30, TODAY())

For last 30 days from maximum sickness end date.

D_Date1 = CALENDAR(MAX('Table'[Sickness End Date])-30, IF(Max('Table'[Sickness End Date].[Date])=BLANK(),TODAY(),MAX('Table'[Sickness End Date])))