Forum Discussion

Dellis81's avatar
Dellis81
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

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&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

     

  • 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

8 Replies

  • 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's avatar
      lbendlin
      Icon for Super User rankSuper 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&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

       

      • Dellis81's avatar
        Dellis81
        Icon for Post Prodigy rankPost 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