Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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")
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)
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
@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")
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?
@JayJayOliveira , Can you share a better data with expected output
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
92 | |
82 | |
70 | |
64 |
User | Count |
---|---|
115 | |
106 | |
96 | |
81 | |
72 |