cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Annual sparkline

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&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='20' points='" & Lines & 
     "'/></svg>"
    
RETURN 
SVGImageURL

 

 

 

Here 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&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='2' points='" & Lines & 
     "'/></svg>"
    
RETURN 
SVGImageURL

 

View solution in original post

Highlighted
Super User II
Super User II

Re: Annual sparkline

@Dellis81 

 

There 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&colon;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 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]))
//calculate(YEAR(MAX('Calendar'[Date])),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]),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 viewbox
VAR 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 sparkline
VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",[Fin Year])
// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL = "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='2' points='" & Lines & "'/></svg>"
RETURN SVGImageURL

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

8 REPLIES 8
Highlighted
Super User II
Super User II

Re: Annual sparkline

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_0-1597527374758.png

 

 

 

Highlighted
Super User II
Super User II

Re: Annual sparkline

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&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='20' points='" & Lines & 
     "'/></svg>"
    
RETURN 
SVGImageURL

 

 

 

Here 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&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='2' points='" & Lines & 
     "'/></svg>"
    
RETURN 
SVGImageURL

 

View solution in original post

Highlighted
Helper V
Helper V

Re: Annual sparkline

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!!!Sparkline Daily to Annual.PNG

 

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

Highlighted
Super User II
Super User II

Re: Annual sparkline

Sparklines are not bars, they are lines.  For bars you need different code. 

https://www.rgraph.net/svg/bar.html

 

Here is what I see

 

lbendlin_0-1597536800797.png

 

Highlighted
Helper V
Helper V

Re: Annual sparkline

Yes, you are right - sparklines are lines :)...

 

When I copy your two measure in - this is what I see - see the red cirlcled below the matrix w/values.   I have also verified both measures are set to an image url

 

I was reading - this problem could be related to browser - I am using Chrome - and should be current.

 

thank you again!

 

SparklineAnnual2.PNG

Highlighted
Super User II
Super User II

Re: Annual sparkline

@Dellis81 

 

There 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&colon;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 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]))
//calculate(YEAR(MAX('Calendar'[Date])),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]),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 viewbox
VAR 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 sparkline
VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ",[Fin Year])
// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL = "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='2' points='" & Lines & "'/></svg>"
RETURN SVGImageURL

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Highlighted
Helper V
Helper V

Re: Annual sparkline

Than you very much - with the help of  @AllisonKennedy  I was able to make work.   Thank you for your awesome support in this community.   Thank you

Highlighted
Helper V
Helper V

Re: Annual sparkline

Thank you Allison - for assisting in the annual sparkline question.   This forum is great - because of people like you.  Thank you

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors