Sparklines showing 12 month period

[Anonymous]

Hi, so I have to implement sparklines in my table using dax code. I found exmaples online that worked by generating a SVG image!

However, I want to modify the code and seem to be hitting some hard ends. This code generates a svg sparkline that takes into Months (MonthID), and fully reacts depending on how I filter the visual. So, if I filter the last year, my sparklines show only the months for that year. However, I need them to show the last 12 months regardless of year. I know how to do this in general, but I can't understand what I need to modify. I tried modifying xMinDate = MAX('History'[MonthID]) -12  but it's not working. Just moving the graph a bit. I am fairly sure I need to modify something in the bolded piece of code below. Any help would be appreciated! 

Sparkline Line orders year =

// Static line color

VAR LineColor = "#01B8AA"

// "Date" field used in this example along the X axis

VAR XMinDate = Min('History'[MonthID])

VAR XMaxDate = MAX('History'[MonthID])

// Obtain overall min and overall max measure values when evaluated for each date

VAR YMinValue = MINX(VALUES('History'[MonthID]),CALCULATE([AvgWeek]))

VAR YMaxValue = MAXX(VALUES('History'[MonthID]),CALCULATE([AvgWeek]))

// Build table of X & Y coordinates and fit to 100 x 100 viewbox

VAR SparklineTable = ADDCOLUMNS(

SUMMARIZE('History','History'[MonthID]),

"X",INT(85 * DIVIDE('History'[MonthID] - XMinDate, XMaxDate - XMinDate)),

"Y",INT(85 * DIVIDE([AvgWeek] - YMinValue,YMaxValue - YMinValue)))

// Concatenate X & Y coordinates to build the sparkline

VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ", [MonthID])

// Add to SVG, and verify Data Category is set to Image URL for this measure

VAR SVGImageURL = IF(HASONEVALUE('CSR'[CSR_Name]) && [AvgWeek]<>blank(),

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

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

"<polyline fill='none' stroke='" & LineColor &

"' stroke-width='3' points='" & Lines &

"'/></svg>",

BLANK())

RETURN SVGImageURL

[v-luwang-msft]

Hi @Anonymous ，

Try :

VAR XMinDate = MIN(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1))
VAR XMaxDate = max(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1)

Refer: https://community.powerbi.com/t5/Desktop/SVG-Sparkline-for-rolling-12-months-in-a-table-matrix/td-p/1554827 

And how  table  'History' get ?

Best Regards

Lucien

[amitchandak]

@Anonymous ,  You can try a measure like that if you need for last 12 month

Sales 12 month =
VAR _Cuur_start = eomonth(today(),-12)+1
VAR _Curr_END = Today()
return
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <= _Curr_END )

The problem is if you select 1 month and want 12 months, then month need to be selected from the last 12

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[KarenFingerhut]

Hi @amitchandak 

Ive just been reviewing this post as I wanted to add sparklines to a KPI report. 

Is there anyway of saying no matter what date you select in your filter the sparkine will then take that date and then go back to the previous 3 month.

So for instance,

if I select period end 30/6/2023 the sparline will show 30/4, 31/5, 30/6

if I select period end 31/5/2023 the sparline will show 31/3, 30/4, 31/5

Does that make sense and is this possible.  You've been ace on other stuff for me so I was hoping you could work your magic again 🙂

Thank you

Kind regards

Karen

[swasim]

Is this resolved ? I am having same requirement . Do u have sample pbix file which is resolved ?

[KarenFingerhut]

Hi @swasim 

The way I fixed it was to create a new dataset and join the sparkline dataset to my main dataset  and fixed the 6 month period I needed. Worked a treat

Thanks Karen

[swasim]

Is it possible to post the implementation pbix file if possible? . A sample one 

[KarenFingerhut]

Its not that easy to post a sample file as I'd have to recreate the dashboard which is quite big with sample data. Let me look in to it