Forum Discussion
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 members that include their individual start dates.
I’m trying to make this a measure, I can get it working for all individual team members but what I’m looking to do is get the total correct as well. The total is my problem.
What I’ve cooked up so far is:
SUMX (
VALUES ( Team[Team Member] ),
CALCULATE (
COUNTROWS ( 'Date' ),
'Date'[Date] >= MIN(Team[Start Date] )))
What is breaking this is the MIN(Team[Start Date]), at least when looking at a total.
If anyone has ideas or suggestions to get this working it would be greatly appreciated!
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
14 Replies
jcawley If you can provide sample data as text can be more specific.
First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8The Date table is just a date table. Just a list of calendar dates and nothing more. The team member table would look like:
Team Member Start Date
John Doe 04/01/2024
Jane Doe 03/01/2024
So if we subtracted TODAY() less these two start dates, we get 23 days and 54 days respectively. The correct total for this would be 77 days but what the measure would do is count 54+54 because of the MIN(). Which is incorrect!jcawley No need for a date table then really. PBIX attached below sig.
Measure = SUMX( SUMMARIZE( 'Table', [Team Member], "__Days", ( TODAY() - MAX('Table'[Start Date] ) ) * 1. ), [__Days] )
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 - _nonWorkingDayHere 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.
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 🤔
