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

 

jcawley's avatar
jcawley
Helper III
2 years ago

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?

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

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

      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!

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        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

         

  • MNedix's avatar
    MNedix
    Solution Sage
    2 years ago

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

     

    • jcawley's avatar
      jcawley
      Helper III
      2 years ago

      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?