Leaves taken in a month

[Anonymous]

Hi Community,

I need help to create a measure to calculate 'total leaves taken' like following (ignore weekends & public holidays):

for example:  emp B took 21 leaves in Sep, 31 leaves in oct & 2 leaves in Nov.

My pbi sample file:

https://1drv.ms/u/s!Ag919_pO_UKrgSNq3-uQTF6KrCRO?e=jcooHH

Many thanks

[Kumail]

Hello @Anonymous 

Seems there is something missing in the data. Emp B took leaves from 10/09/2021 to 2/11/2021 and 2/10/2021 to 6/10/2021 for the same year that couldn't be possible.

Hope this helps to clarify.

Regards

Kumail Raza

Did this help? Kudos are appreciated

[Anonymous]

@Kumail thanks for letting me know. I have corrected it now.

[Jihwan_Kim]

Hi,

I created some more columns in the datedim table and I marked the datedim table as datetable.

Please check the below picture and the attached pbix file.

Leave days count split by month: =
VAR startdate =
MAX ( 'Table'[leave start date] )
VAR enddate =
MAX ( 'Table'[leave end date] )
VAR datetable_between_startenddate =
FILTER (
ALL ( Datedim ),
Datedim[Date] >= startdate
&& Datedim[Date] <= enddate
)
VAR add_leavecount =
ADDCOLUMNS ( datetable_between_startenddate, "@leavecount", 1 )
VAR groupbymonth =
GROUPBY (
add_leavecount,
Datedim[Month name CC],
Datedim[Month number CC],
"@monthlycount", SUMX ( CURRENTGROUP (), [@leavecount] )
)
RETURN
CONCATENATEX (
groupbymonth,
[@monthlycount] & " days in " & Datedim[Month name CC],
" / ",
Datedim[Month number CC]
)

[Anonymous]

@Jihwan_Kim  thanks for your solution. Really appreciate it. Just one thing, I want the data to be presented in a matrix like below:

Note that I have updated emp B leaves 

updated pbi file:

https://1drv.ms/u/s!Ag919_pO_UKrgSNq3-uQTF6KrCRO?e=MswKaF

I'll be very grateful if you could help me presenting the data like above.

Thanks

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly.

Please check the below picture and the attached pbix file.

I disconnected the two tables.

Leave days count: =
VAR startdate =
MAX ( 'Table'[leave start date] )
VAR enddate =
MAX ( 'Table'[leave end date] )
VAR calendartable =
FILTER ( Datedim, Datedim[Date] >= startdate && Datedim[Date] <= enddate )
RETURN
COUNTROWS ( calendartable )

[amitchandak]

@Anonymous ,

You can find work date like

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

But for holiday , better to use a calendar

refer

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

[Anonymous]

@amitchandak thanks, but this doesn't help me much.