Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Syndicate_Admin
Administrator
Administrator

"La consulta especificada es demasiado compleja para ser evaluada como una sola instrucción". Error con el código SVG

Hola

Estoy trabajando en un diagrama de Gantt utilizando el objeto visual estándar de Power BI Matrix con código SVG para visualizar las barras a través de SVG. Las barras funcionan bien, ahora estoy trabajando en una barra de tiempo que debería proporcionar información sobre el año y los meses.

Gantt.png

La barra de tiempo se genera a través del siguiente DAX | SVG y se visualiza en el gráfico de tabla estándar de Power BI, ubicado justo en el encabezado de columna del objeto visual de matriz. Quiero cubrir los años y meses durante un máximo de 10 años, es decir, 132 meses, en consecuencia, el código para el SVG se estaba volviendo bastante largo (cerca de 1500 filas). Al agregar cualquier línea adicional al código, se produce el error: "La consulta especificada es demasiado compleja para evaluarse como una sola instrucción". ¿Hay alguna forma de resolver esto?, el código es muy repetitivo ya que cualquier línea y objeto en la barra de tiempo SVG tiene que crearse por separado.

TestTimeLine = 
VAR _MinDate =
    CALCULATE ( MIN ( Tasks[TaskStartDate] ), ALLSELECTED ( TasksHeader ) )
VAR _MaxDate =
    CALCULATE ( MAX ( Tasks[TaskFinishDate] ), ALLSELECTED ( TasksHeader ) )
VAR _MaxDuration =
    DATEDIFF ( _MinDate, _MaxDate, DAY )
VAR _Scaling =
    DIVIDE ( 512, _MaxDuration )
VAR _EndofFY =
    -- End of first Year
    DATE ( YEAR ( _MinDate ), 12, 31 )
VAR _EndofNY1 =
    -- End of next year 1
    DATE ( YEAR ( _MinDate ) + 1, 12, 31 )
VAR _EndofNY2 =
    DATE ( YEAR ( _MinDate ) + 2, 12, 31 )
VAR _EndofNY3 =
    DATE ( YEAR ( _MinDate ) + 3, 12, 31 )
VAR _EndofNY4 =
    DATE ( YEAR ( _MinDate ) + 4, 12, 31 )
VAR _EndofNY5 =
    DATE ( YEAR ( _MinDate ) + 5, 12, 31 )
VAR _EndofNY6 =
    DATE ( YEAR ( _MinDate ) + 6, 12, 31 )
VAR _EndofNY7 =
    DATE ( YEAR ( _MinDate ) + 7, 12, 31 )
VAR _EndofNY8 =
    DATE ( YEAR ( _MinDate ) + 8, 12, 31 )
VAR _EndofNY9 =
    DATE ( YEAR ( _MinDate ) + 9, 12, 31 )
VAR _EndofNY10 =
    DATE ( YEAR ( _MinDate ) + 10, 12, 31 )
VAR _FYLine =
    DATEDIFF ( _MinDate, _EndofFY, DAY ) * _Scaling
VAR _NY1Line =
    DATEDIFF ( _MinDate, _EndofNY1, DAY ) * _Scaling
VAR _NY2Line =
    DATEDIFF ( _MinDate, _EndofNY2, DAY ) * _Scaling
VAR _NY3Line =
    DATEDIFF ( _MinDate, _EndofNY3, DAY ) * _Scaling
VAR _NY4Line =
    DATEDIFF ( _MinDate, _EndofNY4, DAY ) * _Scaling
VAR _NY5Line =
    DATEDIFF ( _MinDate, _EndofNY5, DAY ) * _Scaling
VAR _NY6Line =
    DATEDIFF ( _MinDate, _EndofNY6, DAY ) * _Scaling
VAR _NY7Line =
    DATEDIFF ( _MinDate, _EndofNY7, DAY ) * _Scaling
VAR _NY8Line =
    DATEDIFF ( _MinDate, _EndofNY8, DAY ) * _Scaling
VAR _NY9Line =
    DATEDIFF ( _MinDate, _EndofNY9, DAY ) * _Scaling
VAR _NY10Line =
    DATEDIFF ( _MinDate, _EndofNY10, DAY ) * _Scaling
VAR _NY1 =
    YEAR ( _MinDate ) + 1
VAR _NY2 =
    YEAR ( _MinDate ) + 2
VAR _NY3 =
    YEAR ( _MinDate ) + 3
VAR _NY4 =
    YEAR ( _MinDate ) + 4
VAR _NY5 =
    YEAR ( _MinDate ) + 5
VAR _NY6 =
    YEAR ( _MinDate ) + 6
VAR _NY7 =
    YEAR ( _MinDate ) + 7
VAR _NY8 =
    YEAR ( _MinDate ) + 8
VAR _NY9 =
    YEAR ( _MinDate ) + 9
VAR _NY10 =
    YEAR ( _MinDate ) + 10
VAR _EndofFM =
    EOMONTH ( _MinDate, 0 ) -- End of first month
VAR _EndofNM1 =
    EOMONTH ( _MinDate, 1 ) -- End of next months 1
VAR _EndofNM2 =
    EOMONTH ( _MinDate, 2 )
VAR _EndofNM3 =
    EOMONTH ( _MinDate, 3 )
VAR _EndofNM4 =
    EOMONTH ( _MinDate, 4 )
VAR _EndofNM5 =
    EOMONTH ( _MinDate, 5 )
VAR _EndofNM6 =
    EOMONTH ( _MinDate, 6 )
VAR _EndofNM7 =
    EOMONTH ( _MinDate, 7 )
VAR _EndofNM8 =
    EOMONTH ( _MinDate, 8 )
VAR _EndofNM9 =
    EOMONTH ( _MinDate, 9 )
VAR _EndofNM10 =
    EOMONTH ( _MinDate, 10 )


..... to VAR _EndofNM132


VAR _FMLine =
    DATEDIFF ( _MinDate, _EndofFM, DAY ) * _Scaling
VAR _NM1Line =
    DATEDIFF ( _MinDate, _EndofNM1, DAY ) * _Scaling
VAR _NM2Line =
    DATEDIFF ( _MinDate, _EndofNM2, DAY ) * _Scaling
VAR _NM3Line =
    DATEDIFF ( _MinDate, _EndofNM3, DAY ) * _Scaling
VAR _NM4Line =
    DATEDIFF ( _MinDate, _EndofNM4, DAY ) * _Scaling
VAR _NM5Line =
    DATEDIFF ( _MinDate, _EndofNM5, DAY ) * _Scaling
VAR _NM6Line =
    DATEDIFF ( _MinDate, _EndofNM6, DAY ) * _Scaling
VAR _NM7Line =
    DATEDIFF ( _MinDate, _EndofNM7, DAY ) * _Scaling
VAR _NM8Line =
    DATEDIFF ( _MinDate, _EndofNM8, DAY ) * _Scaling
VAR _NM9Line =
    DATEDIFF ( _MinDate, _EndofNM9, DAY ) * _Scaling
VAR _NM10Line =
    DATEDIFF ( _MinDate, _EndofNM10, DAY ) * _Scaling

..... to VAR _NM132Line


VAR _NM1TextP =
    -- Months Text position
    _FMLine
        + DIVIDE ( _NM1Line - _FMLine, 2 )
VAR _NM2TextP =
    _NM1Line
        + DIVIDE ( _NM2Line - _NM1Line, 2 )
VAR _NM3TextP =
    _NM2Line
        + DIVIDE ( _NM3Line - _NM2Line, 2 )
VAR _NM4TextP =
    _NM3Line
        + DIVIDE ( _NM4Line - _NM3Line, 2 )
VAR _NM5TextP =
    _NM4Line
        + DIVIDE ( _NM5Line - _NM4Line, 2 )
VAR _NM6TextP =
    _NM5Line
        + DIVIDE ( _NM6Line - _NM5Line, 2 )
VAR _NM7TextP =
    _NM6Line
        + DIVIDE ( _NM7Line - _NM6Line, 2 )
VAR _NM8TextP =
    _NM7Line
        + DIVIDE ( _NM8Line - _NM7Line, 2 )
VAR _NM9TextP =
    _NM8Line
        + DIVIDE ( _NM9Line - _NM8Line, 2 )
VAR _NM10TextP =
    _NM9Line
        + DIVIDE ( _NM10Line - _NM9Line, 2 )

..... to VAR _NM132TextP

VAR _TodayLine =
    DATEDIFF ( _MinDate, TODAY (), DAY ) * _Scaling
VAR _FM =
    LEFT ( FORMAT ( _EndofFM, "mmm" ), 1 )
VAR _NM1 =
    LEFT ( FORMAT ( _EndofNM1, "mmm" ), 1 )
VAR _NM2 =
    LEFT ( FORMAT ( _EndofNM2, "mmm" ), 1 )
VAR _NM3 =
    LEFT ( FORMAT ( _EndofNM3, "mmm" ), 1 )
VAR _NM4 =
    LEFT ( FORMAT ( _EndofNM4, "mmm" ), 1 )
VAR _NM5 =
    LEFT ( FORMAT ( _EndofNM5, "mmm" ), 1 )
VAR _NM6 =
    LEFT ( FORMAT ( _EndofNM6, "mmm" ), 1 )
VAR _NM7 =
    LEFT ( FORMAT ( _EndofNM7, "mmm" ), 1 )
VAR _NM8 =
    LEFT ( FORMAT ( _EndofNM8, "mmm" ), 1 )
VAR _NM9 =
    LEFT ( FORMAT ( _EndofNM9, "mmm" ), 1 )
VAR _NM10 =
    LEFT ( FORMAT ( _EndofNM10, "mmm" ), 1 )

..... to VAR _NM132

VAR _FontSM = "6.5"
VAR _ColourTimeL = "rgb(235,235,235)"
VAR _ColourTL = "rgb(255,190,0)"
VAR _WidtTL = "0.5"
VAR _TL1 = "data&colon;image/svg+xml;utf8,<svg 
    width='512' height='35'
    xmlns='http://www.w3.org/2000/svg'  
    xmlns:xlink='http://www.w3.org/1999/xlink'>
 
        <line x1='0' y1='15' x2='512' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />

        <line x1='" & _FYLine & "' y1='0' x2='" & _FYLine & "' y2='35' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' /> 
        <text x='" & _FYLine + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY1 & "</text>

        <line x1='" & _NY1Line & "' y1='0' x2='" & _NY1Line & "' y2='35' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY1Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY2 & "</text>

        <line x1='" & _NY2Line & "' y1='0' x2='" & _NY2Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY2Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY3 & "</text>

        <line x1='" & _NY3Line & "' y1='0' x2='" & _NY3Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY3Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY4 & "</text>

        <line x1='" & _NY4Line & "' y1='0' x2='" & _NY4Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY4Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY5 & "</text>

        <line x1='" & _NY5Line & "' y1='0' x2='" & _NY5Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY5Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY6 & "</text>

        <line x1='" & _NY6Line & "' y1='0' x2='" & _NY6Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY6Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY7 & "</text>

        <line x1='" & _NY7Line & "' y1='0' x2='" & _NY7Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY7Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY8 & "</text>

        <line x1='" & _NY8Line & "' y1='0' x2='" & _NY8Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY8Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY9 & "</text>

        <line x1='" & _NY9Line & "' y1='0' x2='" & _NY9Line & "' y2='15' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NY9Line + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _NY10 & "</text>

        <line x1='" & _FMLine & "' y1='15' x2='" & _FMLine & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM1TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM1 & "</text>

        <line x1='" & _NM1Line & "' y1='15' x2='" & _NM1Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM2TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM2 & "</text>

        <line x1='" & _NM2Line & "' y1='15' x2='" & _NM2Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM3TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM3 & "</text>

        <line x1='" & _NM3Line & "' y1='15' x2='" & _NM3Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM4TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM4 & "</text>

        <line x1='" & _NM4Line & "' y1='15' x2='" & _NM4Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM5TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM5 & "</text>

        <line x1='" & _NM5Line & "' y1='15' x2='" & _NM5Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM6TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM6 & "</text>

        <line x1='" & _NM6Line & "' y1='15' x2='" & _NM6Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM7TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM7 & "</text>

        <line x1='" & _NM7Line & "' y1='15' x2='" & _NM7Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM8TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM8 & "</text>

        <line x1='" & _NM8Line & "' y1='15' x2='" & _NM8Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM9TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM9 & "</text>

        <line x1='" & _NM9Line & "' y1='15' x2='" & _NM9Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM10TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM10 & "</text>

        <line x1='" & _NM10Line & "' y1='15' x2='" & _NM10Line & "' y2='25' style='stroke:" & _ColourTimeL & ";stroke-width:" & _WidtTL & "' />
        <text x='" & _NM11TextP & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _NM11 & "</text>

   	...... To be continued to 132

        <line x1='" & _TodayLine & "' y1='0' x2='" & _TodayLine & "' y2='35' style='stroke:" & _ColourTL & ";stroke-width:" & _WidtTL & "' />

    </svg>"
RETURN
    _TL1

Br

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

El problema se resuelve con un nuevo código mucho más eficiente. 😀

Gracias de todos modos

TimeLine = 
VAR _MinDate =
    CALCULATE ( MIN ( Tasks[TaskStartDate] ), ALLSELECTED ( TasksHeader ) )
VAR _MaxDate =
    CALCULATE ( MAX ( Tasks[TaskFinishDate] ), ALLSELECTED ( TasksHeader ) )
VAR _MaxDuration =
    DATEDIFF ( _MinDate, _MaxDate, DAY )
VAR _Scaling =
    DIVIDE ( 512, _MaxDuration )
VAR _TodayLine =
    DATEDIFF ( _MinDate, TODAY (), DAY ) * _Scaling
VAR _FontSM = "6.5"
VAR _ColourTimeL = "rgb(235,235,235)"
VAR _ColourTL = "rgb(255,190,0)"
VAR _WidtTL = "0.5"
VAR _YLines =
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                FILTER (
                    ADDCOLUMNS (
                        GENERATESERIES ( _MinDate, _MaxDate, 1 ),
                        "MinDate", _MinDate,
                        "EndofMonth", IF ( [Value] = DATE ( YEAR ( [Value] ), 12, 31 ), [Value] )
                    ),
                    NOT ( ISBLANK ( [EndofMonth] ) )
                ),
                "PosMLine", DATEDIFF ( [MinDate], [EndofMonth], DAY ) * _Scaling
            ),
            "SVGLine",
                VAR _PosMLine = [PosMLine]
                RETURN
                    "<line x1='" & _PosMLine & "' y1='0' x2='" & _PosMLine & "' y2='15' style='stroke:" & _ColourTimeL & "; stroke-width:" & _WidtTL & "' />"
        ),
        [SVGLine]
    )
VAR _YText =
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                FILTER (
                    ADDCOLUMNS (
                        GENERATESERIES ( _MinDate, _MaxDate, 1 ),
                        "MinDate", _MinDate,
                        "EndofMonth", IF ( [Value] = DATE ( YEAR ( [Value] ), 12, 31 ), [Value] )
                    ),
                    NOT ( ISBLANK ( [EndofMonth] ) )
                ),
                "PosMLine", DATEDIFF ( [MinDate], [EndofMonth], DAY ) * _Scaling,
                "YearText", YEAR ( [EndofMonth] ) + 1
            ),
            "SVGText",
                VAR _PosMLine = [PosMLine]
                VAR _Year = [YearText]
                RETURN
                    "<text x='" & _PosMLine + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _Year & "</text>"
        ),
        [SVGText]
    )
VAR _MLines =
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                FILTER (
                    ADDCOLUMNS (
                        GENERATESERIES ( _MinDate, _MaxDate, 1 ),
                        "MinDate", _MinDate,
                        "EndofMonth", IF ( [Value] = EOMONTH ( [Value], 0 ), [Value] )
                    ),
                    NOT ( ISBLANK ( [EndofMonth] ) )
                ),
                "PosMLine", DATEDIFF ( [MinDate], [EndofMonth], DAY ) * _Scaling
            ),
            "SVGLine",
                VAR _PosMLine = [PosMLine]
                RETURN
                    "<line x1='" & _PosMLine & "' y1='15' x2='" & _PosMLine & "' y2='25' style='stroke:" & _ColourTimeL & "; stroke-width:" & _WidtTL & "' />"
        ),
        [SVGLine]
    )
VAR _MText =
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    FILTER (
                        ADDCOLUMNS (
                            GENERATESERIES ( _MinDate, _MaxDate, 1 ),
                            "MinDate", _MinDate,
                            "EndofMonth", IF ( [Value] = EOMONTH ( [Value], 0 ), [Value] )
                        ),
                        NOT ( ISBLANK ( [EndofMonth] ) )
                    ),
                    "PosMLine", DATEDIFF ( [MinDate], [EndofMonth], DAY ) * _Scaling,
                    "PosMLineNext", DATEDIFF ( [MinDate], EOMONTH ( [EndofMonth], + 1 ), DAY ) * _Scaling,
                    "MonthsText", LEFT ( FORMAT ( EOMONTH ( [EndofMonth], + 1 ), "mmm" ), 1 )
                ),
                "Gap", [PosMLineNext] - [PosMLine]
            ),
            "SVGText",
                VAR _PosMText =
                    [PosMLine] + DIVIDE ( [Gap], 2 )
                VAR _MText = [MonthsText]
                RETURN
                    "<text x='" & _PosMText & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _MText & "</text>"
        ),
        [SVGText]
    )
VAR _TL1 = "data&colon;image/svg+xml;utf8,<svg 
    width='512' height='35'
    xmlns='http://www.w3.org/2000/svg'  
    xmlns:xlink='http://www.w3.org/1999/xlink'>
        " & _YLines & "
        " & _YText & "
        " & _MLines & "
        " & _MText & "
        <line x1='" & _TodayLine & "' y1='0' x2='" & _TodayLine & "' y2='35' style='stroke:" & _ColourTL & ";stroke-width:" & _WidtTL & "' />

    </svg>"
RETURN
    _TL1

View solution in original post

1 REPLY 1
Syndicate_Admin
Administrator
Administrator

El problema se resuelve con un nuevo código mucho más eficiente. 😀

Gracias de todos modos

TimeLine = 
VAR _MinDate =
    CALCULATE ( MIN ( Tasks[TaskStartDate] ), ALLSELECTED ( TasksHeader ) )
VAR _MaxDate =
    CALCULATE ( MAX ( Tasks[TaskFinishDate] ), ALLSELECTED ( TasksHeader ) )
VAR _MaxDuration =
    DATEDIFF ( _MinDate, _MaxDate, DAY )
VAR _Scaling =
    DIVIDE ( 512, _MaxDuration )
VAR _TodayLine =
    DATEDIFF ( _MinDate, TODAY (), DAY ) * _Scaling
VAR _FontSM = "6.5"
VAR _ColourTimeL = "rgb(235,235,235)"
VAR _ColourTL = "rgb(255,190,0)"
VAR _WidtTL = "0.5"
VAR _YLines =
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                FILTER (
                    ADDCOLUMNS (
                        GENERATESERIES ( _MinDate, _MaxDate, 1 ),
                        "MinDate", _MinDate,
                        "EndofMonth", IF ( [Value] = DATE ( YEAR ( [Value] ), 12, 31 ), [Value] )
                    ),
                    NOT ( ISBLANK ( [EndofMonth] ) )
                ),
                "PosMLine", DATEDIFF ( [MinDate], [EndofMonth], DAY ) * _Scaling
            ),
            "SVGLine",
                VAR _PosMLine = [PosMLine]
                RETURN
                    "<line x1='" & _PosMLine & "' y1='0' x2='" & _PosMLine & "' y2='15' style='stroke:" & _ColourTimeL & "; stroke-width:" & _WidtTL & "' />"
        ),
        [SVGLine]
    )
VAR _YText =
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                FILTER (
                    ADDCOLUMNS (
                        GENERATESERIES ( _MinDate, _MaxDate, 1 ),
                        "MinDate", _MinDate,
                        "EndofMonth", IF ( [Value] = DATE ( YEAR ( [Value] ), 12, 31 ), [Value] )
                    ),
                    NOT ( ISBLANK ( [EndofMonth] ) )
                ),
                "PosMLine", DATEDIFF ( [MinDate], [EndofMonth], DAY ) * _Scaling,
                "YearText", YEAR ( [EndofMonth] ) + 1
            ),
            "SVGText",
                VAR _PosMLine = [PosMLine]
                VAR _Year = [YearText]
                RETURN
                    "<text x='" & _PosMLine + 5 & "' y='10' font-family='Arial' font-size='9' fill='" & _ColourTimeL & "'>" & _Year & "</text>"
        ),
        [SVGText]
    )
VAR _MLines =
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                FILTER (
                    ADDCOLUMNS (
                        GENERATESERIES ( _MinDate, _MaxDate, 1 ),
                        "MinDate", _MinDate,
                        "EndofMonth", IF ( [Value] = EOMONTH ( [Value], 0 ), [Value] )
                    ),
                    NOT ( ISBLANK ( [EndofMonth] ) )
                ),
                "PosMLine", DATEDIFF ( [MinDate], [EndofMonth], DAY ) * _Scaling
            ),
            "SVGLine",
                VAR _PosMLine = [PosMLine]
                RETURN
                    "<line x1='" & _PosMLine & "' y1='15' x2='" & _PosMLine & "' y2='25' style='stroke:" & _ColourTimeL & "; stroke-width:" & _WidtTL & "' />"
        ),
        [SVGLine]
    )
VAR _MText =
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    FILTER (
                        ADDCOLUMNS (
                            GENERATESERIES ( _MinDate, _MaxDate, 1 ),
                            "MinDate", _MinDate,
                            "EndofMonth", IF ( [Value] = EOMONTH ( [Value], 0 ), [Value] )
                        ),
                        NOT ( ISBLANK ( [EndofMonth] ) )
                    ),
                    "PosMLine", DATEDIFF ( [MinDate], [EndofMonth], DAY ) * _Scaling,
                    "PosMLineNext", DATEDIFF ( [MinDate], EOMONTH ( [EndofMonth], + 1 ), DAY ) * _Scaling,
                    "MonthsText", LEFT ( FORMAT ( EOMONTH ( [EndofMonth], + 1 ), "mmm" ), 1 )
                ),
                "Gap", [PosMLineNext] - [PosMLine]
            ),
            "SVGText",
                VAR _PosMText =
                    [PosMLine] + DIVIDE ( [Gap], 2 )
                VAR _MText = [MonthsText]
                RETURN
                    "<text x='" & _PosMText & "' y='22' text-anchor='middle' font-family='Arial' font-size='" & _FontSM & "' fill='" & _ColourTimeL & "'>" & _MText & "</text>"
        ),
        [SVGText]
    )
VAR _TL1 = "data&colon;image/svg+xml;utf8,<svg 
    width='512' height='35'
    xmlns='http://www.w3.org/2000/svg'  
    xmlns:xlink='http://www.w3.org/1999/xlink'>
        " & _YLines & "
        " & _YText & "
        " & _MLines & "
        " & _MText & "
        <line x1='" & _TodayLine & "' y1='0' x2='" & _TodayLine & "' y2='35' style='stroke:" & _ColourTL & ";stroke-width:" & _WidtTL & "' />

    </svg>"
RETURN
    _TL1

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors