Grouping Calculation

[voglejm]

Hello Everyone!

I'm new to PowerBi and I'm looking for some help. I've been asked to create a calculated field that shows the amount of time that each request was open. The customer desires to see a sum of time that a request was open; however, they are wanting any hold state removed from the sum. So for the example provided below the customer would expect to see that RFS21121 was open for roughly 124 days. 

I'm honestly not even sure where to begin; however, I'm hoping someone can help.

[Nathaniel_C · Accepted solution]

Hi @voglejm ,

From DATEDIFF - google- I would use this function twice, and then subtract 1 from the other.

Maybe add these in a few columns so that you can confirm the correctness of each part, and then circle back and make one column out of the others. I always find it easier to debug.  So the first column might be CALCULATE(MAX(Table[Created], FILTER(ALL(Table), Table[Previous Status] = null, Table[Title])) (or maybe use table[Current Status] = something). It just needs to be consistent. That would be your first date, get the second date, and then use the DateDiff. Then do it all over for the second duration.

One other thing...in your picture the type is shown as ABC123 which is Any...that can create problems.  Always change that.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Syntax

DAXCopy

DATEDIFF(<start_date>, <end_date>, <interval>)  

Parameters

Term Definition

start_date	A scalar datetime value.
end_date	A scalar datetime value Return value.
interval	The interval to use when comparing dates. The value can be one of the following: - SECOND - MINUTE - HOUR - DAY - WEEK - MONTH - QUARTER - YEAR

Return value

The count of interval boundaries crossed between two dates.

[Nathaniel_C]

I would also recommend @MattAllington 's book, Supercharge Power BI.

[Nathaniel_C]

Hi @voglejm ,

So the logic for each TITLE is get the date where the previous status is null, get the date where the Current status is complete. Count the diff, then get the date where the current status is 0.2 hold, get the date where the previous status is 0.2 hold, find the diff. Subtract the second diff from the first diff?  Will these values null complete 0.2 hold, always be the same?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[voglejm]

@Nathaniel_C  - The values may not always be the same, specifically for the Hold status'.  There are many different flavors of hold; however, they are all denoted with .2. For example, there could be a Hold status that is 1.2 or 7.2. Let me know if that helps clear it up. I appreciate any help that you can provide!!

Justin 

[Nathaniel_C]

So does my logic seem to be clear, if we changed the one to contains "hold"?
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[voglejm]

@Nathaniel_C  The logic does make sense; however, I'm unclear on where I would define this logic. Sorry for the ignorance, I'm just not that familiar with PowerBI. If you have any tutorials or other threads you might recommend I'm happy to learn, I just didn't find anything in my initial search.