SVG line chart show last 12 months trend regardless of year moth slicer

[dgdgdg122db]

Hello there,

I'd like to adjust the SVG data to display only the trend for the past 12 months, disregarding any date slicer selections from the report page. Currently, when I select the year-month slicer, the SVG line also filters accordingly, which I don't want.

Thank you 

SVG=

VAR XMinDate = MIN(sales[posting_date])

VAR XMaxDate = MAX(sales[posting_date])

VAR YMinValue = MINX(Values(Dates[start of month]),CALCULATE([Revenue]))

VAR YMaxValue = MAXX(Values(Dates[start of month]),CALCULATE([Revenue]))

VAR SparklineTable = ADDCOLUMNS(

    SUMMARIZE(sales,Dates[start of month]),

        "X",INT(580 * DIVIDE(Dates[start of month] - XMinDate, XMaxDate - XMinDate)),

        "Y",INT(100 * DIVIDE([Reveune] - YMinValue,YMaxValue - YMinValue)))

VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ", Dates[start of month])

VAR SVGImageURL =

   "data:image/svg+xml;utf8," &

    "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 600 120'>"  &

     "<polyline fill='%231A0033' fill-opacity='0.1' stroke='%231A0033' stroke-width='3' points=' 0 600 " & Lines & " 600,120'/></svg>"

RETURN SVGImageURL

[dgdgdg122db]

Thank you Rena, it does not work, the svg is still filtered by the report level slicer. Normally I can use ALL/ Allselecetd/ removefilters to disregard any filter coming from the outside of my current measure. but I am struggling to do it in the SVG.  The goal is to just show the last 12 months trend , igroning any dates selected from the report page.  for example. if the year month slicer on the report page is selected:  Mar-2024, the SVG should still show the last 12 months trend, not Mar-2024 data

[Anonymous]

Hi @dgdgdg122db ,

Please try the following formula:

SVG =
VAR CurrentDate =
    TODAY ()
VAR XMinDate =
    EOMONTH ( CurrentDate, -13 ) + 1 // Start of the month 12 months ago
VAR XMaxDate =
    EOMONTH ( CurrentDate, -1 ) + 1 // Start of the current month
VAR YMinValue =
    CALCULATE (
        MIN ( sales[Revenue] ),
        FILTER (
            ALL ( sales ),
            sales[posting_date] >= XMinDate
                && sales[posting_date] < XMaxDate
        )
    )
VAR YMaxValue =
    CALCULATE (
        MAX ( sales[Revenue] ),
        FILTER (
            ALL ( sales ),
            sales[posting_date] >= XMinDate
                && sales[posting_date] < XMaxDate
        )
    )
VAR SparklineTable =
    ADDCOLUMNS (
        SUMMARIZE ( sales, sales[posting_date] ),
        "X",
            INT ( 580 * DIVIDE ( sales[posting_date] - XMinDate, XMaxDate - XMinDate ) ),
        "Y",
            INT ( 100 * DIVIDE ( [Revenue] - YMinValue, YMaxValue - YMinValue ) )
    )
VAR Lines =
    CONCATENATEX ( SparklineTable, [X] & "," & 100 - [Y], " ", sales[posting_date] )
VAR SVGImageURL = "data&colon;image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 600 120'>" & "<polyline fill='%231A0033' fill-opacity='0.1' stroke='%231A0033' stroke-width='3' points=' 0 600 " & Lines & " 600,120'/></svg>"
RETURN
    SVGImageURL

Best Regards

[Anonymous]

Hi @dgdgdg122db ,

Please update the formula as below and check if it can return the expected result...

SVG =
VAR XMinDate =
    MIN ( sales[posting_date] )
VAR XMaxDate =
    MAX ( sales[posting_date] )
VAR YMinValue =
    MINX ( VALUES ( Dates[start of month] ), CALCULATE ( [Revenue] ) )
VAR YMaxValue =
    MAXX ( VALUES ( Dates[start of month] ), CALCULATE ( [Revenue] ) )
VAR SparklineTable =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( sales, sales[posting_date] >= TODAY () - 365 ),
            Dates[start of month]
        ),
        "X",
            INT ( 580 * DIVIDE ( Dates[start of month] - XMinDate, XMaxDate - XMinDate ) ),
        "Y",
            INT ( 100 * DIVIDE ( [Revenue] - YMinValue, YMaxValue - YMinValue ) )
    )
VAR Lines =
    CONCATENATEX (
        SparklineTable,
        [X] & "," & 100 - [Y],
        " ",
        Dates[start of month]
    )
VAR SVGImageURL = "data&colon;image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 600 120'>" & "<polyline fill='%231A0033' fill-opacity='0.1' stroke='%231A0033' stroke-width='3' points=' 0 600 " & Lines & " 600,120'/></svg>"
RETURN
    SVGImageURL

Best Regards