cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Overlapping Dates times aging calculation

Hello Community!
I need help! I'm trying to calculate the aging between submit time and closed time of incidents by process,

But in my case, there is an overlapping scenario happening for most of the process:

P000777 Process has some incidents, highlighted rows show up the overlapping scenario.

The right calculation in this example is to consider the min non overlapping data and the last data, and then calculate the difference between those two dates.I have been trying a lot of PowerQuery solutions but my database is really heavy and its not running well those possible soluctions even with buffering tables setting.

Tks all!

PROCESS ID INCIDENT ID SUBMIT DATE              CLOSED DATE              Incorrect Aging (Days)

 P0000777 86950787 09/01/2023 07:07:00 09/07/2023 17:45:00 6 P0000551 86946646 08/25/2023 15:13:00 08/28/2023 11:07:00 3 P0000777 86946248 08/24/2023 22:43:00 09/01/2023 12:08:00 8 P0000777 86944999 08/24/2023 05:35:00 09/04/2023 05:01:00 11 P0000777 86944827 08/23/2023 05:51:00 09/05/2023 02:00:00 13 P0000551 86732351 08/05/2023 09:09:00 08/15/2023 18:17:00 10 P0000777 86731911 08/03/2023 11:11:00 09/03/2023 08:12:00 31 P0000777 86731910 08/01/2023 10:15:00 08/02/2023 18:00:00 1

4 REPLIES 4
Community Support

Hi @PauloSMoura ,

I suggest you to try code as below to create a measure.

``````Diff Days =
VAR _LASTDATE =
CALCULATE (
MAX ( 'Table'[CLOSED DATE] ),
ALLEXCEPT ( 'Table', 'Table'[PROCESS ID] )
)
VAR _LASTSTART =
CALCULATE (
MAX ( 'Table'[SUBMIT DATE] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[PROCESS ID] ),
'Table'[CLOSED DATE] = _LASTDATE
)
)
VAR _LIST =
CALENDAR ( DATEVALUE ( _LASTSTART ), DATEVALUE ( _LASTDATE ) )
VAR _MINDATE =
CALCULATE (
MIN ( 'Table'[SUBMIT DATE] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[PROCESS ID] ),
DATEVALUE ( 'Table'[CLOSED DATE] ) IN _LIST
)
)
RETURN
DATEDIFF ( _MINDATE, _LASTDATE, DAY )``````

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

Hello Tks for the Reply!

I've try to reply this dax formula on my database and found some problems,

The difference with this project is it is showing only 14 days and it's not capturing the whole context of the table. Also I have a question. The aging number is the date diff between the overlapping dates, but if I want to have, I like the overlapping diff and also the non-overlapping difference using the same example:

Red ones is overlapping but the first one green is not overlapping with any value, the correct aging is the sum between the overlapping range and the non overlapping.

Tks again for your reply i really appreciate that!

TKSS!

Super User

pretty much all of these are overlapping. what makes the one starting at 8/3 so special?

Regular Visitor

Thanks for the reply!

In this scenario, pretty much all projects have overlapping incidents, what makes the one in the example special is that it is the first one that starts the overlapping stream, so I need the submit date for this one (start of the overlapping stream) and the last date (close date) at end of the stream.

That pattern can happen in more than one time at. I mean i can have like 1~3 months of overlapping and after that an isolated value without overlapping and then start the overlapping again.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors