topic Re: Sparklines showing 12 month period in DAX Commands and Tips

Sparklines showing 12 month period

Anonymous — Wed, 17 Nov 2021 11:36:00 GMT

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

Re: Sparklines showing 12 month period

amitchandak — Wed, 17 Nov 2021 12:46:19 GMT

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

Re: Sparklines showing 12 month period

v-luwang-msft — Tue, 23 Nov 2021 02:45:32 GMT

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

Re: Sparklines showing 12 month period

KarenFingerhut — Thu, 17 Aug 2023 12:51:55 GMT

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

Re: Sparklines showing 12 month period

swasim — Sun, 10 Mar 2024 02:17:58 GMT

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

Re: Sparklines showing 12 month period

KarenFingerhut — Tue, 12 Mar 2024 13:05:15 GMT

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

Re: Sparklines showing 12 month period

swasim — Tue, 12 Mar 2024 13:09:10 GMT

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

Re: Sparklines showing 12 month period

KarenFingerhut — Tue, 12 Mar 2024 13:11:35 GMT

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