Skip to main content
Showing results for 
Search instead 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

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)

P00007778695078709/01/2023 07:07:0009/07/2023 17:45:006
P00005518694664608/25/2023 15:13:0008/28/2023 11:07:003
P00007778694624808/24/2023 22:43:0009/01/2023 12:08:008
P00007778694499908/24/2023 05:35:0009/04/2023 05:01:0011
P00007778694482708/23/2023 05:51:0009/05/2023 02:00:0013
P00005518673235108/05/2023 09:09:0008/15/2023 18:17:0010
P00007778673191108/03/2023 11:11:0009/03/2023 08:12:0031
P00007778673191008/01/2023 10:15:0008/02/2023 18:00:001



Community Support
Community Support

Hi @PauloSMoura ,


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

Diff Days =
        MAX ( 'Table'[CLOSED DATE] ),
        ALLEXCEPT ( 'Table', 'Table'[PROCESS ID] )
        MAX ( 'Table'[SUBMIT DATE] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[PROCESS ID] ),
            'Table'[CLOSED DATE] = _LASTDATE
        MIN ( 'Table'[SUBMIT DATE] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[PROCESS ID] ),
            DATEVALUE ( 'Table'[CLOSED DATE] ) IN _LIST

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.

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!



Super User
Super User

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


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.

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.