Forum Discussion
Annual sparkline
Hello!
I have found a very useful sparkline measure that works well on a daily basis. I am now wanting to tweak - and attempt to come up with a annual sparkline.
As in the image shown - I have a calendar yr slicer - and then in the right most image, is a measure reflective of the slicer - and the hope is to have a sparkline showing values as represented in the matrix. End result - is when I select a year - the measure value changes, but sparkline continues to show the annual trend..
Below is where I am at on YR Sparkline. I'm guessing the problem is within the Summarize function.
Sparkline Line x Yr =
// Static line color
VAR LineColor = "#01B8AA"
// "Date" field used in this example along the X axis
VAR XMinDate = CALCULATE(MIN('Calendar'[Fin Year]),ALL('Calendar'[Fin Year]))
VAR XMaxDate = calculate(MAX('Calendar'[Fin Year]),all('Calendar'[Fin Year]))
// Obtain overall min and overall max measure values when evaluated for each date
VAR YMinValue = CALCULATE(MINX(VALUES('Calendar'[Fin Year]),CALCULATE([Measure Value])),ALLEXCEPT('Calendar','Calendar'[Fin Year]))
VAR YMaxValue = calculate(MAXX(VALUES('Calendar'[Fin Year]),CALCULATE([Measure Value])),ALLEXCEPT('Calendar','Calendar'[Fin Year]))
// Build table of X & Y coordinates and fit to 100 x 100 viewbox
VAR SparklineTable = ADDCOLUMNS(
SUMMARIZE('Calendar','Calendar'[Fin Year]),
"X",INT(100 * DIVIDE(max('Calendar'[Fin Year]) - XMinDate, XMaxDate - XMinDate)),
"Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue)))
// Concatenate X & Y coordinates to build the sparkline
VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",'Calendar'[Fin Year])
// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL =
"data: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='20' points='" & Lines &
"'/></svg>"
RETURN
SVGImageURL
The original daily sparkline measure is working - and is also found in provided file. As far as I know - the Variables with XMinDate,XMinValue, etc connotations are working properly. The only thing different betwen the two measures - I added a calendar table so I could work with year
File link: https://1drv.ms/u/s!AmBVCme14p7xlV_uC0f_E0payT3N?e=Yp8sPw
This is how the annual sum would potentially look like . Stroke width seems excessive 🙂
Sparkline Line x Yr = // Static line color VAR LineColor = "#01B8AA" // "Date" field used in this example along the X axis VAR XMinDate = CALCULATE(MIN('Calendar'[Fin Year]),ALL('Calendar'[Fin Year])) VAR XMaxDate = calculate(MAX('Calendar'[Fin Year]),all('Calendar'[Fin Year])) // Obtain overall min and overall max measure values when evaluated for each date VAR YMinValue = CALCULATE(MINX(VALUES('Calendar'[Fin Year]),[Measure Value]),ALLEXCEPT('Calendar','Calendar'[Fin Year])) VAR YMaxValue = calculate(MAXX(VALUES('Calendar'[Fin Year]),[Measure Value]),ALLEXCEPT('Calendar','Calendar'[Fin Year])) // Build table of X & Y coordinates and fit to 100 x 100 viewbox VAR SparklineTable = ADDCOLUMNS( SUMMARIZE('Calendar','Calendar'[Fin Year]), "X",INT(100 * DIVIDE('Calendar'[Fin Year] - XMinDate, XMaxDate - XMinDate)), "Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue))) // Concatenate X & Y coordinates to build the sparkline VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",'Calendar'[Fin Year]) // Add to SVG, and verify Data Category is set to Image URL for this measure VAR SVGImageURL = "data: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='20' points='" & Lines & "'/></svg>" RETURN SVGImageURLHere is another version that is a bit leaner. Keep in mind that the sparkline is not shown by year (or date) so the filter context is basically only the totals.
Sparkline Line x Yr = // Static line color VAR LineColor = "#01B8AA" // "Date" field used in this example along the X axis VAR XMinDate = MIN('Calendar'[Fin Year]) VAR XMaxDate = MAX('Calendar'[Fin Year]) // Obtain overall min and overall max measure values when evaluated for each date VAR YMinValue = MINX(VALUES('Calendar'[Fin Year]),[Measure Value]) VAR YMaxValue = MAXX(VALUES('Calendar'[Fin Year]),[Measure Value]) // Build table of X & Y coordinates and fit to 100 x 100 viewbox VAR SparklineTable = ADDCOLUMNS( SUMMARIZE('Calendar','Calendar'[Fin Year]), "X",INT(100 * DIVIDE('Calendar'[Fin Year] - XMinDate, XMaxDate - XMinDate)), "Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue))) // Concatenate X & Y coordinates to build the sparkline VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",'Calendar'[Fin Year]) // Add to SVG, and verify Data Category is set to Image URL for this measure VAR SVGImageURL = "data: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='2' points='" & Lines & "'/></svg>" RETURN SVGImageURLThere was a small typo in the DAX you copied from lbendlin - the colon was converted to text and needs to stay as colon when defining the VAR SVGImageURL = data:image See the screenshot posted by lbendlin rather than the code that was altered when the html of the post was auto edited.
Also, it looks like you are trying to make this work to display all years even when only 1 year is selected, so you need to use ALL (not ALLEXCEPT). lbendlin I believe has not used a slicer to select a single financial year.
Finally, the measure below will find the MAX year in your Calendar table, but you may wish to use MAXX instead if you want max year that actually has data for it.
See if this measure below helps: replace the bit in RED below with an actual colon :
Sparkline Line x Yr1 =//Static line colorVAR LineColor = "#01B8AA"// "Date" field used in this example along the X axisVAR XMinDate = CALCULATE(MIN('Calendar'[Fin Year]),ALL('Calendar'[Fin Year]))VAR XMaxDate =//calculate(YEAR(MAX('Calendar'[Date])),all('Calendar'[Fin Year])) // Obtain overall min and overall max measure values when evaluated for each dateCALCULATE(MAX('Calendar'[Fin Year]),ALL('Calendar'[Fin Year]))VAR YMinValue = CALCULATE(MINX(VALUES('Calendar'[Fin Year]),[Measure Value]),ALL('Calendar'[Fin Year]))VAR YMaxValue = calculate(MAXX(VALUES('Calendar'[Fin Year]),[Measure Value]),ALL('Calendar'[Fin Year]))// Build table of X & Y coordinates and fit to 100 x 100 viewboxVAR SparklineTable = ADDCOLUMNS( SUMMARIZE(ALL('Calendar'),'Calendar'[Fin Year]), "X",INT(100 * DIVIDE('Calendar'[Fin Year] - XMinDate, XMaxDate - XMinDate)), "Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue)))
//Concatenate X & Y coordinates to build the sparklineVAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",[Fin Year])// Add to SVG, and verify Data Category is set to Image URL for this measureVAR SVGImageURL = "data: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='2' points='" & Lines & "'/></svg>"RETURN SVGImageURL
8 Replies
- lbendlin
Super User
What do you want the annual value to be ? the average of the daily values? the sum? something else? Your code example seems to imply sum.
Small comment on your original measure, lines 8 and 9:
- lbendlin
Super User
This is how the annual sum would potentially look like . Stroke width seems excessive 🙂
Sparkline Line x Yr = // Static line color VAR LineColor = "#01B8AA" // "Date" field used in this example along the X axis VAR XMinDate = CALCULATE(MIN('Calendar'[Fin Year]),ALL('Calendar'[Fin Year])) VAR XMaxDate = calculate(MAX('Calendar'[Fin Year]),all('Calendar'[Fin Year])) // Obtain overall min and overall max measure values when evaluated for each date VAR YMinValue = CALCULATE(MINX(VALUES('Calendar'[Fin Year]),[Measure Value]),ALLEXCEPT('Calendar','Calendar'[Fin Year])) VAR YMaxValue = calculate(MAXX(VALUES('Calendar'[Fin Year]),[Measure Value]),ALLEXCEPT('Calendar','Calendar'[Fin Year])) // Build table of X & Y coordinates and fit to 100 x 100 viewbox VAR SparklineTable = ADDCOLUMNS( SUMMARIZE('Calendar','Calendar'[Fin Year]), "X",INT(100 * DIVIDE('Calendar'[Fin Year] - XMinDate, XMaxDate - XMinDate)), "Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue))) // Concatenate X & Y coordinates to build the sparkline VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",'Calendar'[Fin Year]) // Add to SVG, and verify Data Category is set to Image URL for this measure VAR SVGImageURL = "data: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='20' points='" & Lines & "'/></svg>" RETURN SVGImageURLHere is another version that is a bit leaner. Keep in mind that the sparkline is not shown by year (or date) so the filter context is basically only the totals.
Sparkline Line x Yr = // Static line color VAR LineColor = "#01B8AA" // "Date" field used in this example along the X axis VAR XMinDate = MIN('Calendar'[Fin Year]) VAR XMaxDate = MAX('Calendar'[Fin Year]) // Obtain overall min and overall max measure values when evaluated for each date VAR YMinValue = MINX(VALUES('Calendar'[Fin Year]),[Measure Value]) VAR YMaxValue = MAXX(VALUES('Calendar'[Fin Year]),[Measure Value]) // Build table of X & Y coordinates and fit to 100 x 100 viewbox VAR SparklineTable = ADDCOLUMNS( SUMMARIZE('Calendar','Calendar'[Fin Year]), "X",INT(100 * DIVIDE('Calendar'[Fin Year] - XMinDate, XMaxDate - XMinDate)), "Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue))) // Concatenate X & Y coordinates to build the sparkline VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",'Calendar'[Fin Year]) // Add to SVG, and verify Data Category is set to Image URL for this measure VAR SVGImageURL = "data: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='2' points='" & Lines & "'/></svg>" RETURN SVGImageURL- Dellis81
Post Prodigy
Thank you for your response. Apparently in my orginal post - the image below failed to make it. I would expect to have 5 bars (2010,2011,etc) in the sparkline representing the sum of each year (as shown in the matrix). The sparkline that is working - is the original sparkline by date.
Yes, I am fearful of the line width also - but can only tackle once I see what is going on.
I saved my latest efforts in the same file you have access to.
Again - thank you!!!
I did insert both of your formulas - and in both cases, the result was a blank image. I do have set as an image url