Forum Discussion

jcawley's avatar
jcawley
Helper III
2 years ago
Solved

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 m...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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's avatar
jcawley
Helper III
2 years ago

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!

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

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]
    )