Help with DAX, convert SUMIFS

[ksquirt]

I'm brand new to DAX and reasonably new to Power BI.  I'm trying to convert an Excel formula to Power BI.  Google is not quite helping with one piece.  I need to sum column D when there is a match in column E AND a match in column L.

Excel: =SUMIFS(D:D,E:E,E2,L:L,L2)

DAX: Sum of hours = CALCULATE(SUM('Timesheet Data May 2025 2025'[Units],(FILTER(all('Timesheet Data May 2025 2025'[Contractor User Name])),(FILTER(all('Timesheet Data May 2025 2025'[Work Date]))))))

[v-dineshya · Accepted solution]

Hi @ksquirt ,

Thank you for reaching out to the Microsoft Community Forum.

please follow below steps.

1.  Created sample data based on inputs. refer snap.

2. Created measure "Sum of Hours Measure", As you are expecting, when User Name and  work date match, it will  sum the units.

Sum of Hours Measure =
CALCULATE(
SUM('Timesheet'[Units]),
FILTER(
ALL('Timesheet'),
'Timesheet'[Contractor User Name] = SELECTEDVALUE('Timesheet'[Contractor User Name]) &&
'Timesheet'[Work Date] = SELECTEDVALUE('Timesheet'[Work Date])
)
)
Please refer output snaps and PBIX file.

In that snap, For Contractor User Name (Alice), the sum of work date for Alice on May 1st 2025 is  9 (2+3+4)=9

The Result is as you expected.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[ksquirt]

This worked!  Thank you so much!

[bhanu_gautam]

@ksquirt Try Using

dax
Sum of hours =
CALCULATE(
SUM('Timesheet Data May 2025 2025'[Units]),
FILTER(
'Timesheet Data May 2025 2025',
'Timesheet Data May 2025 2025'[Contractor User Name] = EARLIER('Timesheet Data May 2025 2025'[Contractor User Name]) &&
'Timesheet Data May 2025 2025'[Work Date] = EARLIER('Timesheet Data May 2025 2025'[Work Date])
)
)

[ksquirt]

Thanks!  It looks like it's adding Units where the User Name is the same, but not the work date.  I need it to sum the units when both matches.

What does 'Earlier' do exactly?

[bhanu_gautam]

@ksquirt 

Returns the current value of the specified column in an outer evaluation pass of the mentioned column.

EARLIER is useful for nested calculations where you want to use a certain value as an input and produce calculations based on that input.

Try using

dax
Sum of hours =
CALCULATE(
SUM('Timesheet Data May 2025 2025'[Units]),
FILTER(
'Timesheet Data May 2025 2025',
'Timesheet Data May 2025 2025'[Contractor User Name] = EARLIER('Timesheet Data May 2025 2025'[Contractor User Name]) &&
'Timesheet Data May 2025 2025'[Work Date] = EARLIER('Timesheet Data May 2025 2025'[Work Date])
)
)

[ksquirt]

It seems to be adding the units everytime the user name changes.