Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
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]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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
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 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
142 | |
71 | |
64 | |
52 | |
50 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |