Forum Discussion
Helper IIIYet Another Matrix Total Problem
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
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.
Helper IIIThanks 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 🤔