Help with Complex Summarize Measure

[Anonymous]

Here is what I’m trying to accomplish:

I have a table of employees, and a date table in my model. The employee table has a terminated date column.

In my report, I have a date slicer. I would like to create a measure that sums the DATEDIFF in days between the MIN(Date) selected in the date slicer and the terminated date, and sum the total difference in days for EACH employee.

What I have tried:

Created a measure that successfully calculates the DATEDIFF in days between the MIN(employee_term_date) and the MIN(Date), which is on the right track, but only measures the DATEDIFF of the minimum employee terminated date.

I also tried creating a calculated column in the employee table which calculates the DATEDIFF between the MIN(Date) and the employee_term_date, however, of course this does not work because the scope of MIN(Date) ignores the report filter and grabs the minimum date from the date table.

What I’m thinking:

I think what I need to do is a somewhat complex measure which uses the calculate function to somehow iterate through each resource, calculate the date diff between the MIN(Date) and the employee_term_date, and then return the sum of the DATEDIFF for each resource. Or maybe even using the SUMMARIZE function?

Anyone reading this tried something like this before? Still getting used to DAX functions, SQL would be so much easier 😊

[parry2k]

If you can share sample data in excel file that will help to provide you the solution. It is very much doable.

[Anonymous]

Does it help if I include an example of the SQL query I would construct here?:

select sum(d.Days2Adj)
from
(select resourceID, DATEDIFF('d', @minDate, term_date) as Days2Adj from employees)d

[parry2k]

hmmm, not the logic, just the sample data, you can remove any sensitive information , share it using excel.

[Anonymous]

Sample employee table

employeeID	term_date
1	1/27/2018
2
3
4	2/5/2018
5
6
7	4/15/2018
8
9
10
11	5/1/2018
12

[parry2k · Accepted solution]

Use sumx function to sum your existing measure you calculated for days

Total Terminated Days = SUMX(Table1, [your measure])

[Anonymous]

Brilliant! I think SUMX and I will become friends. Thank you 🙂