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
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
__Result
Thanks 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!
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