build Visual calendar- leave management

[Anonymous]

I have to build a dashboard for employee annual leave calendar. The calendar needs to be visual by month, showing total employees abseent on the date.   

P.s - I have tried using calendar template by MAC but unfortunately that gives continuous dates and due to large number of employees and interval between leaves, it doesnt work for me.

PFA data details.

Dataset 1 - Employee ID, start date, end date, leave category. 

Dataset 2  - Employee ID, Future Leaves available, Leave Balance.

Thanks,

[amitchandak · Accepted solution]

@Anonymous , Refer if Matrix and conditional formatting can help

Matrix as Project plan Visual: https://youtu.be/R25QoiyoSVs

[v-luwang-msft]

Hi @Anonymous ,

If you want to return employee ID ,refer the below:

new table:

Table2 = CROSSJOIN('DATE',Dateset1)

 Then create the below column:

Column = if(Table2[start date]<='TABLE2'[Date]&&Table2[end date]>='TABLE2'[Date],1,BLANK())

After filter:

Best Regards

Lucien

[Anonymous]

@v-luwang-msft  Cross Join isn't working. 
I need a calculated column next to 'Absent employee count' column to return 'Employee ID' on the days of leave in date calendar. 
for example, if employee if (x) is absent on 22 july(start date) - 26 july(end date), employee id (y) is absent on 24 july(start date) - 25 july(end date). I want calculate column to represent the following

Date	count	employee id (on leave)
22 july	1	x
23 july	1	x
24 july	2	x,y
25 july	2	x,y
26 july	1	x

I have a seperate reference table (attached below) for the duration of start and end date. So, if i can use this table for the calculated column. that would do the job as well. 

[Anonymous]

Hi ! The reference table has the list of dates based on code recovery category. Could you please identify the steps to calculate employees count based recovery code category.

[Anonymous]

@v-luwang-msft Thanks for the solution. Could you please provide step to add calculated column that returns employee ID instead of count.
Thanks,

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

According your provided,refer the below:

dataset1:

Base  on this ,create a new date table:

DATE = CALENDAR("2022,4,1","2022,4,30")

Then use the below dax to create a new column:

COUNT = CALCULATE(DISTINCTCOUNT(Dateset1[Employee ID]),FILTER(Dateset1,Dateset1[start date]<='DATE'[Date]&&Dateset1[end date]>='DATE'[Date]))

Then you will get the below:（even with continuous dates  ,it will still work)

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak · Accepted solution]

@Anonymous , Refer if Matrix and conditional formatting can help

Matrix as Project plan Visual: https://youtu.be/R25QoiyoSVs