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!
jcawleyDo you insist of using the MIN function?
If I understood the problem correctly, then there could easily be two options:
1. create an additional column with a simple formula like:
DateDiff = TODAY()-[Start Date]Then you simply add the DateDiff to a card (PowerBI should do the sum for you) or create a measure with SUM
2. create a measure as below:
Tenure = SUMX(Names,DATEDIFF(Names[Start Date],TODAY(),DAY))(assuming that 'Names' is your Team Members table)
If this solved your problem then please mark it as the solution.
Best,