How to get sum of two columns (date/time)

[SolarWill]

Hello guys,

New to the forum and Power BI.

So basically I have a system that exports data that i'd like to filter through to get important info.

I have two columns important for knowing how to solve this:

1. Status

2. Created (Date/Time)

Look at this picture:

So essentially, I want to find the sum of the last column, "Created" BUT only when the Status is "Open" and "Closed".

So in summary - we are subtracting the times between the first and last item in that list.

How can I achieve this? We can ignore the "In Progress" item.

I'd like to output in just hours in 00:00:00 format - we can ignore the date.

Thanks!

[artemus]

There are several ways to do this. One is to create a measure:

DateDiff = CALCULATE(DATEDIFF(MIN(MyTable[Created]), MAX(MyTable[Created]),HOUR),MyTable[Status] = "Open" || MyTable[Status] = "Closed") & "00:00"

Then just put the measure in a card 

[SolarWill]

Thanks for the quick reply - that is very cool

However I think I did something wrong, output is 00:00:

[artemus]

The measure wiill only work in a card visual, as the min/max is scoped to the row. If you want to get around this, add to calculate:

CALCULATE(..., ALL(MyTable))

I think I understand what you want in the total column, but not what you expect in each row.

[SolarWill]

Sorry, I am new to Power BI - where do I add:

ALL(myTableNameHere)?

I added to my existing calculate formula with syntax errors

[artemus]

Like:

DateDiff = CALCULATE(DATEDIFF(MIN(MyTable[Created]), MAX(MyTable[Created]),HOUR),MyTable[Status] = "Open" || MyTable[Status] = "Closed", ALL(MyTableName)) & "00:00"

Note: If you do this you should get the same value for each row.

[SolarWill]

Gotcha, thanks.

I am getting 12400:00 for all rows as you mentinod - why is this?

I don't want to take too much of your time. If I can just subtract the time 3:47:22 PM (Open) from the 6:42:11 PM (Closed) for example, and output the answer: 2:55:12 hours or whatever the exact number is - that would be great

[artemus · Accepted solution]

Oh, I think I understand, you want to do it for each unique title (and you want to include minutes and seconds). In that case, use ALLEXCEPT and just use subtraction:

DateDiff = CALCULATE(MAX(MyTable[Created]) - MIN(MyTable[Created]),MyTable[Status] = "Open" || MyTable[Status] = "Closed", ALLEXCEPT(MyTable,MyTable[Title]))

You will also need to format the column by clicking on the measure in the FIELDS panel. Then in the top ribbon, click Modeling and change the format to a Time format to remove the year 1899 part.

Note: Replace MyTable[Title] with MyTble[Id] if you have a unique id for each request.

[SolarWill]

Thanks a lot! After a little tweaking in the measure I was able to produce the numbers. Thank-you!