cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Help with Complex Summarize Measure

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 😊

1 ACCEPTED SOLUTION
Super User

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

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

6 REPLIES 6
Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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```
Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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
Super User

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

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.