Vacation calendar with overlaps using gantt chart

[Bluenred22]

Dear @ValtteriN, 

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=11...

Regards, 

Bluenred22

[ValtteriN]

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"

[Bluenred22]

@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

[ValtteriN]

Hi,

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

[Bluenred22]

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 🙂 

[ValtteriN]

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.

[Bluenred22]

It works @ValtteriN  thanks for the assist. 

Is it possible to give every child a different color ?

Please let me know. 

Regards, 

Bluenred22

[ValtteriN · Accepted solution]

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.

[Bluenred22]

@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 ?