Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PauloSMoura
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:

 

PauloSMoura_1-1695994699404.png

 

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

 

 

4 REPLIES 4
v-rzhou-msft
Community Support
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.

vrzhoumsft_0-1696237629003.png

 

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,

PauloSMoura_0-1696361701139.png

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:

PauloSMoura_1-1696361987755.png

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!

 

lbendlin
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

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.