Handling sparklines in specific period of time and how to increase sparkline width

[Anonymous]

Hi everyone,

I have implemented sparklines in matrix table for historical data as years, now I'm trying to reproduce it but as monthly basis (last 12 month) for an specific KPI, but i cannot select a period of time(12 last months) for DateTable as SUMMARIZE doesn't handle table variable if I filtered in dax; also I cannot expand width of sparkline , it always stays as static square svg. Here I attached my dax code, could anyone good at dax help me?.

Thank you in advance

Sparkline = 
var LineColor = "#363636"
var FillColor = "#E0E0E0"
var MaxDotColor = "#649398"
var MinDotColor = "#D9655D"
var FirstDotColor = "#363636"
var LastDotColor = "#363636"
var NumOfDashes =0
var DataPointPoint = distinctcount(DateTable[Date-Month])
var StrokeWidth = 
    switch(           
        TRUE(),
        DataPointPoint>24,3,
        DataPointPoint>12,4,
        5
    )
var CircleSize = 
    switch(
        TRUE(),
        DataPointPoint>24,8,
        DataPointPoint>12,9,
        10
    )
var ViewboxOffset = 
    switch(
        TRUE(),
        DataPointPoint>24,-8,
        DataPointPoint>12,-9,
        -10
    )
var XMinDate = MIN(DateTable[Date-Month])
var XMaxDate = MAX(DateTable[Date-Month])

var YMinValue=minx(values(DateTable[Date-Month]),calculate([KPI Last 12]))
var YMaxValue=maXx(values(DateTable[Date-Month]),calculate([KPI Last 12]))

var SparklineTable = ADDCOLUMNS(
    SUMMARIZE(DateTable,DateTable[Date-Month]),
        "X",INT(100*DIVIDE(DateTable[Date-Month]-XMinDate,XmaxDate-XminDate)),
        "Y",INT(100*DIVIDE([KPI Last 12]-YMinValue,YmaxValue-YminValue)))

var Lines = CONCATENATEX(SparklineTable,[X] & "," & 100 - [Y]," ",[Date-Month])

var MaxSparkYValue = maxx(SparklineTable,100 - [Y])
var MaxSparkXValue = maxx(filter(SparklineTable,[Y] = MaxSparkYValue),[X])
var MinSparkYValue = minx(SparklineTable,100 - [Y])
var MinSparkXValue = minx(filter(SparklineTable,[Y] = MinSparkYValue),[X])

var FirstSparkYValue = minx(filter(SparklineTable,[Date-Month]=XMinDate),[Y])
var FirstSparkXValue = minx(filter(SparklineTable,[Date-Month]=XMinDate),[X])
var LastSparkYValue = maxx(filter(SparklineTable,[Date-Month]=XMaxDate),[Y])
var LastSparkXValue = maxx(filter(SparklineTable,[Date-Month]=XMaxDate),[X])

var url = 
    "data:image/svg+xml;utf8," & 
    "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 " & ViewboxOffset & " 100 120'>" &
    "<polyline 
    fill='"& FillColor  & " '
    stroke='"& LineColor  & " '
    stroke-width='"& StrokeWidth & " '
    stroke-linecap='round' 
    stroke-linejoin='round' 
    stroke-dasharray='"& NumOfDashes & "'
    points='"& Lines  &
    "'/>" &
    "<circle cx='"& FirstSparkXValue & "' cy='" &100 -FirstSparkYValue & "' r='" & CircleSize & "' fill='" & LastDotColor &"' />" &
    "<circle cx='"& LastSparkXValue & "' cy='" &100 -LastSparkYValue & "' r='" & CircleSize & "' fill='" & LastDotColor &"' />" &
    "<circle cx='"& MaxSparkXValue & "' cy='" &100 -MaxSparkYValue & "' r='" & CircleSize & "' fill='" & MaxDotColor &"' />" &
    "<circle cx='"& MinSparkXValue & "' cy='" &100 -MinSparkYValue & "' r='" & CircleSize & "' fill='" & MinDotColor &"' />" &
    "</svg>"
return url

[TomMartens · Accepted solution]

Hey @Anonymous ,

please check if these links are providing what you are looking for

• SVG Sparklines - Line - Microsoft Power BI Community
• (355) SVG Image Sparklines on Rows in Power BI Tables - YouTube

If not, please consider to create a pbix that contains sample data but still reflects your data model, upload the pbix to onedrive or dropbox and share the link.

Hopefully, this will provide what you are looking for, at least provide some new idead.

Regards,
Tom