Yet Another Matrix Total Problem

[jcawley]

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 members that include their individual start dates.

I’m trying to make this a measure, I can get it working for all individual team members but what I’m looking to do is get the total correct as well. The total is my problem.

What I’ve cooked up so far is:

SUMX (

    VALUES ( Team[Team Member] ),

    CALCULATE (

        COUNTROWS ( 'Date' ),

        'Date'[Date] >= MIN(Team[Start Date] )))

What is breaking this is the MIN(Team[Start Date]), at least when looking at a total.

If anyone has ideas or suggestions to get this working it would be greatly appreciated!

[v-nuoc-msft]

Hi @jcawley 

@MNedix @Greg_Deckler Thank you very much for your prompt help. Allow me to offer some thoughts here.

If you want to check the term and remove non-working days. You will need to create another table that records holidays.

Also, you need to create a relationship between the dates table and the holidays table.

Here's some dummy data

“Team”

“Dates”

"Holidays"

First, create a measure to determine if the date is a non-working day and a holiday.

IsWeekend = 
VAR _holiday = SELECTEDVALUE('Holidays'[Holiday])
RETURN
IF(
    SELECTEDVALUE('Dates'[Date]) = _holiday 
    || 
    WEEKDAY(SELECTEDVALUE('Dates'[Date]), 2) > 5, 
    1, 
    0
)

Then, create a measure to calculation tenure.

Tenure = 
var _totalDays = DATEDIFF(SELECTEDVALUE('Team'[Start Date]), TODAY(), DAY)
var _nonWorkingDay = 
    SUMX(
        FILTER(
            'Dates', 
            'Dates'[Date] >= SELECTEDVALUE('Team'[Start Date]) 
            && 
            'Dates'[Date] <= TODAY() 
            && 
            'Dates'[IsWeekend] = 1
        ), 
        'Dates'[IsWeekend]
    )
RETURN _totalDays - _nonWorkingDay

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jcawley]

Thanks for the reply! This one is SO close! 

The only problem I am having with this one is using a date filter to see just 2024, and the total is negative for some reason 🤔

[Greg_Deckler]

@jcawley If you can provide sample data as text can be more specific. 

First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[jcawley]

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!

[MNedix]

@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,

[Greg_Deckler]

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

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

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

[jcawley]

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?

[MNedix]

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]

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?

[Greg_Deckler]

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

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 · Accepted solution]

@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