Forum Discussion
Yet 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
The 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]
)
Thank you for the reply! I unfortunatly do need the date table - I do not want to count weekends or Holidays and I have a column in my date table that I've called 'Date'[Workday?] that accounts for those with a true/false. I was hoping if I simplified the problem I'd be able to tak on that last bit myself!
Any ideas on how to navigate around that last hurdle?jcawley Well, it's a simple change but I don't understand the logic you want as far as what date you actually want from the date table.
Measure = VAR __Date = MINX( FILTER( ALL('Dates'), [Date] >= TODAY() && [WorkDayFlag] = 1 ),[Date] ) VAR __Result SUMX( SUMMARIZE( 'Table', [Team Member], "__Days", ( __Date - MAX('Table'[Start Date] ) ) * 1. ), [__Days] ) RETURN __ResultThanks again, Greg! This unfortuanly does not work - this doesn't remove holidays or weekends. The code you provided just finds the minmum date that is not a holiday or weekend. I need to remove all subsequent holidays/weekends from the output!
I don't know if you already solved it but if not, you can also use the below:
Tenure = SUMX(Names,NETWORKDAYS(Names[Joined date],TODAY(),1))However, the above calculates only the working days, it does not take into account Holidays since I don't have a holiday table (to be used as the 4th parameter of the NETWORKDAYS function).
Thanks for the reply! I have holidays as part of my 'Date' table! It gets wrapped up in a column that is called 'Date'[Workday?] and is true/false.
Any ideas?
In your file, the same result can be achieved with this
Tenure = SUMX('Table',DATEDIFF('Table'[Start Date],TODAY(),DAY))I fully understand the logic behind your measure but could you please tell me if there is a "hidden" benefit when compared to mine? Also, what does the " *1. " do?
Best,
MNedix Using simple math tends to keep calculations in the storage engine versus the formula engine which tends to be faster. The * 1. just ensures that things get returned as a number.
