Forum Discussion

JayJayOliveira's avatar
JayJayOliveira
New Member
6 years ago
Solved

Exclude conflicting dates in date difference calculation

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!

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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")

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      JayJayOliveira
      New Member
      6 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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