Exclude conflicting dates in date difference calculation

[JayJayOliveira]

Hello everyone!

I'm having trouble excluding date values that are between "bigger" intervals.
Can someone please help? 🙂

I find it better to explain through an example (will use integer values to explain better, but the intention is to do this with dates):

FROM            TO              DIFF
1                    3                 2
4                    10               6
5                    6                 1
6                    11               5

The values 5,6 and 10 (from the FROM and TO columns) are between the inverval [4,11]

What I want to be able to do is to calculate the SUM from the unconflicted intervals [1,3] and [4,11].

Is this possible to do?

Thank you!

[Anonymous]

HI @JayJayOliveira,

Based on my test, I found it is recursive calculation related requirements that dax formula not able to achieve. (power bi does not contain row/column index, if you want to use previous calculation result, you need to manually write formulas and setting filter conditions as previous row contents)

Recursion in DAX 

For example, below is a calculated column formula that I used to check related records and expand the range:

RelatedRange = 
VAR temp =
    FILTER (
        'Sample',
        COUNTROWS (
            INTERSECT (
                GENERATESERIES ( [Start], [End] ),
                GENERATESERIES ( EARLIER ( 'Sample'[Start] ), EARLIER ( 'Sample'[End] ) )
            )
        ) > 0
    )
VAR rStart =
    MINX ( temp, [Start] )
VAR rEnd =
    MAXX ( temp, [End] )
RETURN
    rStart & "~" & rEnd

It works properly but you can find it still not getting the 'final' expand ranges, you need to calculation on the result of above calculation column with the same logic and duplicate these operations until no intersect range existed. (this is the real range your formula need to be calculated)

I'd like to suggest you do these in query editor which support recursive. (recursive calculate custom function will reduce the processing performance and cost huge amount of resource)

Recursive Functions in Power BI / Power Query 

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@JayJayOliveira , Please find the file where I have created an overlapping flag

You need create an index column or make join only > or <

Try like

future conflict = if(ISBLANK( COUNTX(FILTER(Sheet1,  EARLIER([from Date]) >= [from Date] && EARLIER(Sheet1[from Date])<=([to Date]) && [Index] <> EARLIER([Index])),[from  Date])),"NoConflict","Conflict")

 or

future conflict = if(ISBLANK( COUNTX(FILTER(Sheet1,  EARLIER([from Date]) > [from Date] && EARLIER(Sheet1[from Date])<([to Date]) ),[from  Date])),"NoConflict","Conflict")

[JayJayOliveira]

hey @amitchandak ! Thanks for your response, it worked perfectly on many cases.

But on this one specifically we have an issue. Because of a 30 day conflict it is descarding the 2.9 value.


I wonder if we could just discard the 30 day conflict?

[amitchandak]

@JayJayOliveira , Can you share a better data with expected output