showing which Date ranges are overlapping in Power BI

[Bluenred22]

Hello, 

I am trying to show overlapping dates in Power BI. I managed to show that there are overlaps (using counts). However, I dont know how to show the date ranges that have overlapping in the data. Could you please show how this is done 🙂

attached is the file and a screenshot of the report. @GPower   

Final Vacation Report.pbix

 

[Bluenred22]

thanks for the reply @lbendlin im trying to show a highlight of the date ranges that have overlapping. 

 is it possible to do somthing like this and hightlight overlaping date ranges with a different color. 🙂 

Regards, 

Bluenred22

[lbendlin]

That is a bad idea.  I will not offer you a solution that involves a lot of horizontal scrolling.  You can consider something like this

[Bluenred22]

Hmm, I understand that this is a bad Idea but I need to show the overlapping from the range of dates and who are thos names within the calendar 

[lbendlin]

see attached. Same DAX, basically.

[Bluenred22]

what is the DAX for that @lbendlin ?

[Bluenred22]

Hello @lbendlin apologies kindly find the new link to the file: https://drive.google.com/file/d/12M6mOhkxRh68H8kZP48MRzm7BNo9A4k1/view?usp=share_link

Regards, 

Bluenred22

[lbendlin]

see attached.

ov = number of days with overlaps

# name = number of people on vacation in (part of)  that period.

[Bluenred22]

could you kindly explain how did you get  the OV and # name as I am trying to replicate it on the acutal report. I found a visual that can create a gant chart and show bras for the dates taken however I still want to color the overlaping dates.  kindly find attached: https://1drv.ms/u/s!AqREvlcyX-9Dgvlj43Yc_b0vo2Oz2g?e=aC3e5h @collinq @slorin guys if anyone has any tip on the case please let me know also 🙂 

[lbendlin]

OverlappingLeaves = 
var a = SUMMARIZECOLUMNS('Leaves'[Name],'Leaves'[Start],'Leaves'[End],'Date'[Date])
var b = ADDCOLUMNS(a, "ov", if([Start]<=[Date] && [End]>=[Date],1,0))
var c = summarize(b,[Date],"ct",var d=[Date] return sumx(filter(b,[Date]=d),[ov]))
return countrows(filter(c,[ct]>1))

var a:  Create a cross join of users, their leaves  (could be multiple) and dates

var b: check if the individual date falls into the individual leave period

var c: for each date calculate the number of users that are on leave on that date

Then you can decide what to do with the result. Either a simple flag that yes, overlap exists, or an indication how many users are on vacation on the same date.

[Bluenred22]

what I want to know is how you added the OV and #name separately in the Values section @lbendlin 

[lbendlin]

you can drag as many measures as you want into the Values area of a visual.  If it doesn't work for you then you may already have a field in the Legends area. Remove that, and try again.

[Bluenred22]

Dear @lbendlin , 

I have managed to show the available dates instead but this is only happing between two vacations. However, I would like to show available dates after the last vacation forward. I have indicated as an example where the availablity should show (in orange arrows). Also I have notesed that availablity in the middle is not appearing for some vacations. the trick that I used to get it is to appeend the tables after creating a duplicate of the table with some Dax columns involved

kindly find the link to the file. https://drive.google.com/file/d/1dn3bZl7VhIoDrkcZSiMqH9gB9o6q_mM2/view?usp=drive_link

Regards, 

Bluenred22

[lbendlin]

access denied.

What do you need to show the available period for? Isn't that implied?

[Bluenred22]

I thought I could get the overlaps by getting availability period first. 

However I still want to flag the overlaps within this gantt chart. 

[lbendlin]

That's not what a Gantt chart is for. Use a matrix or a custom chart like Deneb that can show value ranges.

[lbendlin]

Please check the link to the file.