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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors