topic Work around for membership days in DAX Commands and Tips

Work around for membership days

supergallager34 — Wed, 19 Aug 2020 09:11:17 GMT

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?

Re: Work around for membership days

amitchandak — Wed, 19 Aug 2020 09:24:28 GMT

supergallager34 , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

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

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

Re: Work around for membership days

Anonymous — Wed, 19 Aug 2020 14:23:17 GMT

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...