Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

conditional filtering help

Hi all, 

I am trying to build a table of Net Working Days (or days worked) for a group of 9 team members. I am running into an interesting problem that needs a solution. You can see a snip of 3 tables below. The first column just has Names for each row which I've omitted. The dilemma I've run into is that the team has not been static over the time period (from Jan-23 - Apr-24). We had one new person join the team and one leave the team. The crux of the problem is that Net Working Days measure is defined as such:  US Days Worked = ('US Days Worked'[US Workdays Per Month] - (Count('US Days Worked'[Date]). Essentially netting PTO out from US Working Days for each team member. If you look at the PTO table in the middle you can see for Apr-24 in the first row, this is the first month this person joined our team. However in the bottom table there is no logic to account for this, so every month prior to 4/24 is tied to the first row in the first table when it should be zero. Similar issue in the second row from bottom. This person left in Jan-24, so the table on the bottom should show zero from there on ... but still shows values from the top table. 

I'm sure there's a DAX solution or some other way to fix this ... I'm just not sure what that might be. Would really appreciate any insights or advice.

Thanks!

Steve

p.s. quick update 

I created a new table called US Start End Date Logic with binary logic. All values are set to 1 with the exception for the two rows described above. For those months where the team member had not yet joined or had left the team ... those are set to zero. So now I just need to figure out some DAX that says IF this persons value in the Start End Date table is zero, THEN zero, ELSE  US Days Worked = ('US Days Worked'[US Workdays Per Month] - (Count('US Days Worked'[Date]).

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Based on the description, please try the following dax formula:

    Working Days = 
CALCULATE(
    SUM('US Days Worked'[US Workdays Per Month]) - COUNT('US Days Worked'[Date]),
    FILTER(
        'US Start End Date Logic',
        'US Start End Date Logic'[Value] = 1
    )
)

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.