Forum Discussion

Bluenred22's avatar
Bluenred22
Icon for Helper I rankHelper I
2 years ago
Solved

Vacation calendar with overlaps using gantt chart

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=113146205425990914425&rtpof=true&sd=true

 

 

 

Regards, 

 

Bluenred22

  • ValtteriN's avatar
    ValtteriN
    2 years ago

    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.

8 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      Bluenred22
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        ValtteriN
        Icon for Community Champion rankCommunity Champion
        2 years ago

        Hi,

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