Forum Discussion

jcawley's avatar
jcawley
Icon for Helper III rankHelper III
2 years ago
Solved

Yet Another Matrix Total Problem

Salutations. So, I want to find the sum of all employees’ tenure in days. How long has everyone collectively been here? I have two tables. One is a date table and the other is a table of team m...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    jcawley That was the purpose of the IsWorkDay flag which you could add to your Date table or just change the DAX a bit to exclude holidays. Use EXCEPT. There wouldn't be so much back and forth on this if you had just described your situation fully the first time around.

    Measure = 
  VAR __Date = MINX( EXCEPT( FILTER( ALL('Dates'), [Date] >= TODAY() & [WorkDayFlag] = 1 ), 'Holidays' ),[Date] )
  VAR __Result
    SUMX(
        SUMMARIZE( 'Table', [Team Member], "__Days", ( __Date - MAX('Table'[Start Date] ) ) * 1. ),
        [__Days]
    )
RETURN
  __Result

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi jcawley 

 

MNedix Greg_Deckler Thank you very much for your prompt help. Allow me to offer some thoughts here.

 

If you want to check the term and remove non-working days. You will need to create another table that records holidays.

 

Also, you need to create a relationship between the dates table and the holidays table.

 

 

Here's some dummy data

 

“Team”

 

“Dates”

 

"Holidays"

 

First, create a measure to determine if the date is a non-working day and a holiday.

 

IsWeekend = 
VAR _holiday = SELECTEDVALUE('Holidays'[Holiday])
RETURN
IF(
    SELECTEDVALUE('Dates'[Date]) = _holiday 
    || 
    WEEKDAY(SELECTEDVALUE('Dates'[Date]), 2) > 5, 
    1, 
    0
)

 

Then, create a measure to calculation tenure.

 

Tenure = 
var _totalDays = DATEDIFF(SELECTEDVALUE('Team'[Start Date]), TODAY(), DAY)
var _nonWorkingDay = 
    SUMX(
        FILTER(
            'Dates', 
            'Dates'[Date] >= SELECTEDVALUE('Team'[Start Date]) 
            && 
            'Dates'[Date] <= TODAY() 
            && 
            'Dates'[IsWeekend] = 1
        ), 
        'Dates'[IsWeekend]
    )
RETURN _totalDays - _nonWorkingDay

 

Here is the result.

 

 

Regards,

Nono Chen

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

 

  • jcawley's avatar
    jcawley
    Icon for Helper III rankHelper III
    2 years ago

    Thanks for the reply! This one is SO close! 

    The only problem I am having with this one is using a date filter to see just 2024, and the total is negative for some reason 🤔