topic Re: Vacation calendar with overlaps using gantt chart in DAX Commands and Tips

Vacation calendar with overlaps using gantt chart

Bluenred22 — Tue, 06 Feb 2024 09:50:58 GMT

I trying to create a gantt chart calendar using vacation date ranges. However, I want to show available dates of employees and also flag and show overlapping date ranges within the gantt chart. could you kindly assisst. Kindly find the link to the report: https://drive.google.com/file/d/1hKW5_mEcXLwNuUreS7mQ3wk_qw5QnJXb/view?usp=share_link

excel sheet data: https://docs.google.com/spreadsheets/d/1u-gLLbJjAkaCDvRdEw5U8-cmmk3SJx-D/edit?usp=share_link&ouid=113146205425990914425&rtpof=true&sd=true

Regards,

Bluenred22

Re: Vacation calendar with overlaps using gantt chart

ValtteriN — Tue, 06 Feb 2024 17:54:58 GMT

Hi,

This solution might be a bit complex and I have done it with mock data due to security limitations, but here is my approach:

In this example I am creating a SVG measure to create the Gant chart instead of using custom visual. This allows for unlimited customizations.

Mockdata calculated table:

GantDemo =

var r1 = RANDBETWEEN(1,5)/10

var r2 =r1+RANDBETWEEN(1,5)/10 RETURN

ADDCOLUMNS(ADDCOLUMNS(GENERATESERIES(0,30,1),"StartDate",TODAY()+ RANDBETWEEN(-30,90)),"EndDate",[StartDate]+RANDBETWEEN(1,3),

"StartDate2",[StartDate]+r1,

"EndDate2",[StartDate]+r2,

"Parent","A" & RANDBETWEEN(1,4),"Child",

"X" & RANDBETWEEN(1,10000))

Dax:

Gantt3 =

var _Gmindate = CALCULATE(MIN(GantDemo[StartDate]),ALLSELECTED(GantDemo))

var _Gmaxdate = CALCULATE(Max(GantDemo[EndDate]),ALLSELECTED(GantDemo))

var _Gdiff = DATEDIFF(_Gmindate,_Gmaxdate,HOUR)

var _basewidth = 650

VAR _TodayLine =

DIVIDE(

DATEDIFF (

_Gmindate,

TODAY (),

hour

),_Gdiff)

*_basewidth

var _DynamicTextGant =

VAR _hrefList = //Here we generate dynamic listing based on the current parent element the vtable gets rows for each child element. The rectangles get dynamic coordinates based on the filter context.This way the duration of the task is simulated.Generateseries + conanatex is used as for loop

var _t1 = VALUES(GantDemo[Child]) //virtual table in filter context used to get the amount of elements

return

ADDCOLUMNS(

_t1,

"CombinedText",

"<rect id='fill' x='" &

DIVIDE(

DATEDIFF(

_Gmindate,

CALCULATE(MIN(GantDemo[StartDate]), ALL(GantDemo), GantDemo[Child] = EARLIER([Child])),

HOUR

_Gdiff

)*_basewidth

//^^dynamic location

"' y='20' width='" &

DIVIDE(

DATEDIFF(

CALCULATE(MIN(GantDemo[StartDate]), ALL(GantDemo), GantDemo[Child] = EARLIER([Child])),

CALCULATE(MAX(GantDemo[EndDate]), ALL(GantDemo), GantDemo[Child] = EARLIER([Child])),

HOUR

_Gdiff

) * _basewidth &

"' height='40' fill='hsl(195, 100%, "&10+ROWNUMBER(_t1)&"%)'></rect>" //hsl will change the light value of the color based on the instances in filter context

//^^dynamic width

//------------------------------------------------------------

"<rect id='fill' x='" &

DIVIDE(

DATEDIFF(

_Gmindate,

CALCULATE(MIN(GantDemo[StartDate2]), ALL(GantDemo), GantDemo[Child] = EARLIER([Child])),

HOUR

_Gdiff

)*_basewidth &

"' y='20' width='" &

DIVIDE(

DATEDIFF(

CALCULATE(MIN(GantDemo[StartDate2]), ALL(GantDemo), GantDemo[Child] = EARLIER([Child])),

CALCULATE(MAX(GantDemo[EndDate2]), ALL(GantDemo), GantDemo[Child] = EARLIER([Child])),

HOUR

_Gdiff

) * _basewidth &

"' height='40' fill='Green'></rect>"

)

return

CONCATENATEX(

_hrefList,

[CombinedText],

)

var _DynamicTextMonth = //same logic as above get a line for each End of Month

VAR _hrefList2 =

var _t2 = VALUES('Calendar'[EMonth])

return

ADDCOLUMNS(

_t2,

"CombinedText",

"<line x1='"& DIVIDE(

DATEDIFF (

_Gmindate,

[EMonth],

hour

),_Gdiff)

*_basewidth&"' y1='0' x2='"& DIVIDE(

DATEDIFF (

_Gmindate,

[EMonth],

hour

),_Gdiff)

*_basewidth&"' y2='100' style='stroke:Blue; stroke-width:2; stroke-dasharray: 10, 5;' />"

"<text filter='url(#solid)' x='"&DIVIDE(

DATEDIFF (

_Gmindate,

[EMonth],

hour

),_Gdiff)

*_basewidth+10&"' y='100' fill='Black' font-weight='bold' font-family='Helvetica Neue, sans-serif' >" & FORMAT([EMonth]+1,"MMM") &" </text>")

return

CONCATENATEX(

_hrefList2,

[CombinedText],

)

var _render = "data&colon;image/svg+xml;utf8,<svg xmlns='http://www.w3.org/2000/svg' xmlns:xlink='http://www.w3.org/1999/xlink'

width='100%' height='100%' viewBox='0 0 "&_basewidth&" 100'

style='background: Gray'

& _DynamicTextGant & _DynamicTextMonth &

"<text filter='url(#solid)' x='0' y='100' fill='Black' font-weight='bold' font-family='Helvetica Neue, sans-serif' >" & FORMAT(_Gmindate,"YYYY/MM/DD") &" </text>" &

"<text filter='url(#solid)' x='"&_basewidth-80&"' y='100' fill='Black' font-weight='bold' font-family='Helvetica Neue, sans-serif' >" & FORMAT(_Gmaxdate,"YYYY/MM/DD") &" </text>" &

"<rect id='marker' x='"&_TodayLine&"' y='0' width='2' height='100' fill='Red'></rect>

</svg>"

return

_render

Explanations:

Red line = today
Dashed line = month change
boxes change colors dynamically based on the amount of rows in filter context (note the hsl). This can indicate overlapping values.
Note that the measure's data type needs to be "image url"

Re: Vacation calendar with overlaps using gantt chart

Bluenred22 — Tue, 06 Feb 2024 14:08:42 GMT

ValtteriN many thanks for the support. There is one question in my head...is the DAX coding in this solution considered hard coding as the data is coming from sharepoint list and its changing every now and then.

Could you also send me the pbix file so I can take a deep look at your solution.

Regards,

Bluenred22

Re: Vacation calendar with overlaps using gantt chart

ValtteriN — Tue, 06 Feb 2024 17:51:12 GMT

Hi,

The solution is dynamic. The example creates new data with RANDBETWEEN. It also works with slicers.
Here is a sample .pbix:

Re: Vacation calendar with overlaps using gantt chart

Bluenred22 — Thu, 08 Feb 2024 06:50:19 GMT

Thanks for the reply ValtteriN so in that case you mentioned "Note that the measure's data type needs to be "image url"" so how do I convert my data into image URL? Also how do I add the data into the demo model as I am not an expert in Dax frankly 🙂

Re: Vacation calendar with overlaps using gantt chart

ValtteriN — Thu, 08 Feb 2024 07:11:40 GMT

Hi,

You can convert the measures data category here:

To use the measure with your data. You can use the corresponding fields for the columns. E.g.
'gantdemo'[StartDate] -> 'YourTable'[YourStartDate] and 'Calendar'[Emonth] -> 'YourCalendar'[EndOfMonth].

[Child] column refers to the dimension column. In my example I am using [parent] and [child] column structure. In your case this column would be name.

This section is optional:

//------------------------------------------------------------

"<rect id='fill' x='" &

DIVIDE(

DATEDIFF(

_Gmindate,

CALCULATE(MIN(GantDemo[StartDate2]), ALL(GantDemo), GantDemo[Child] = EARLIER([Child])),

HOUR

_Gdiff

)*_basewidth &

"' y='20' width='" &

DIVIDE(

DATEDIFF(

CALCULATE(MIN(GantDemo[StartDate2]), ALL(GantDemo), GantDemo[Child] = EARLIER([Child])),

CALCULATE(MAX(GantDemo[EndDate2]), ALL(GantDemo), GantDemo[Child] = EARLIER([Child])),

HOUR

_Gdiff

) * _basewidth &

"' height='40' fill='#007452'></rect>"

)

The [StartDate2] and [EndDate2] create a section within the time period. In your case this might not me recuired. If you remove the above dax you need to add bracket and remove the "&" before the section.

Re: Vacation calendar with overlaps using gantt chart

Bluenred22 — Thu, 08 Feb 2024 08:04:43 GMT

It works ValtteriN thanks for the assist.

Is it possible to give every child a different color ?

Please let me know.

Regards,

Bluenred22

Re: Vacation calendar with overlaps using gantt chart

ValtteriN — Thu, 08 Feb 2024 09:23:39 GMT

Yea,

Here is an example of this (rows 45 to 47 of the dax):

_Gdiff

) * _basewidth &

var _rand = RANDBETWEEN(1,4) return

"' height='"&40-_rand&"' fill='"&SWITCH(_rand,1,"#D64550",2,"Blue",3,"Green",4,"#D9B300")&"'></rect>")

Instead of _rand you could use something like employee number. SELECTEDVALUE('YourTable'[EmpID])

Then use this in switch. E.g. SWITCH(_empid,1200,"#D64550",2,"Blue",3,"Green",4,"#D9B300")

Here the id 1200 gets the hexcode color #D64550.

Re: Vacation calendar with overlaps using gantt chart

Bluenred22 — Thu, 15 Feb 2024 12:45:53 GMT

ValtteriN how do I color the overlapping sections red in that case? as different color shades were not welcomed by users. Another thing is there a way to show which are the overlapping dates in a seprate table ?