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

     

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

 

MNedix's avatar
MNedix
Solution Sage
2 years ago

Greg_Deckler 

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,

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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.