Work around for membership days

[supergallager34]

Hello, 

I have been trying to create membership days for employees based on their start date and end date and our holiday table. My current calculated column looks like this, 

Var TBL_Date = Calendar('tEmployee'[Enter_Date],'tEmployee'[Leave_Date])

Var TBL_Finaldt = 
ADDCOLUMNS(TBL_Date, 
"Work Days", If(WEEKDAY([Date],2)>5,0,1)
"Off Days", IFERROR(LOOKUPVALUE('tCompanyCalendar'[Day_Count],'tCompanyCalendar'[Date],[Date],0))

Return
If(SUMX(TBL_Finaldt, IF[Work Days] = 1 && [Off Days] =1,1,0))=0,1,(SUMX(TBL_Finaldt,IF([Work Days] = 1 && [Off Days] =1,1,0))))

However, I currently have employees who will be starting in the future and this formula is not working for them and causing error overall. Is there a way I can block that? 

[Anonymous]

Hi there.

This code is not only ugly, it'll be very slow. Whether you're going to experience a slowdown or not depends on how much data is kept in the model. But code that uses: iferror, lookupvalue, a virtual table created on the fly, addcolumns, many if's... will be agonizingly slow since all operations will be handled by Formula Engine. For instance, iferror is known to be slow and should be avoided as much as possible.

Why don't you move holidays to your Calendar table? Just mark a day with TRUE if it's a holiday and FALSE if not. This simple change will make the code much easier on the eye and faster. You won't have to even create the TBL_Date table on the fly...

[amitchandak]

@supergallager34 , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Or this file, check the second page - Datediff with and without week end

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

or

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0