Forum Discussion

thod's avatar
thod
Helper I
3 years ago
Solved

Display latest 3 data points in data label

Hi all,

I need to create a measure that can be used as a custom label to only show the latest n number of periods in a line chart.
The line chart displays a Year, Month, Date hierarchy, so the measure show be dynamic and display latest 3 years when on the year level, last 3 months when on the month level and latest 3 days when drilling down to days in the visualisation.

How can the measure be done?

Br.,
Thomas

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi thod 

    You can refer to the following measure

    Measure 4 = var _topyear=TOPN(3,SUMMARIZE(ALLSELECTED('Table'[Year]),[Year]),[Year],DESC)
var b=CONVERT(SELECTEDVALUE('Table'[Month]),STRING)
var c=CONVERT(SELECTEDVALUE('Table'[Date]),STRING)
return IF(ISINSCOPE('Table'[Date].[Day]),IF(CONTAINSSTRING(SELECTEDVALUE('Table'[Max date]),c),SUM('Table'[Column1]),"NA"),IF(ISINSCOPE('Table'[Date].[Month]),IF(CONTAINSSTRING(SELECTEDVALUE('Table'[Max month]),b),SUM('Table'[Column1]),"NA"),IF(SELECTEDVALUE('Table'[Year]) in _topyear,SUM('Table'[Column1]),"NA")))

    Output

     

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi thod 

    You can refer to the following example

    Sample data 

     

    1.Create two calculated column

    Max month =
VAR a =
    FILTER ( 'Table', [Year] = EARLIER ( 'Table'[Year] ) )
VAR b =
    SUMMARIZE ( a, [Month] )
RETURN
    CONCATENATEX ( TOPN ( 3, b, [Month], DESC ), [Month], "," )

    Max date =
VAR a =
    FILTER (
        'Table',
        [Year] = EARLIER ( 'Table'[Year] )
            && [Month] = EARLIER ( 'Table'[Month] )
    )
RETURN
    CONCATENATEX ( TOPN ( 3, a, [Date], DESC ), [Date], "," )

    Then create a measure

    Measure = var _topyear=TOPN(3,SUMMARIZE(ALLSELECTED('Table'[Year]),[Year]),[Year],DESC)
var b=CONVERT(SELECTEDVALUE('Table'[Month]),STRING)
var c=CONVERT(SELECTEDVALUE('Table'[Date]),STRING)
return IF(ISINSCOPE('Table'[Date].[Day]),CALCULATE(SUM('Table'[Column1]),FILTER('Table',CONTAINSSTRING([Max date],c))),IF(ISINSCOPE('Table'[Date].[Month]),CALCULATE(SUM('Table'[Column1]),FILTER('Table',CONTAINSSTRING([Max month],b))),CALCULATE(SUM('Table'[Column1]),FILTER('Table',[Year] in _topyear))))

    Output

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • thod's avatar
      thod
      Helper I
      3 years ago

      Hi Yolo Zhu,
      Thanks for the reply. Not exactly what I want, or maybe I am doing it wrong.
      So, when the linechart is on year and want datalabels shown for year 2023, 2022 and 2021. When you drill down to months, I want datalabels to show July, June and May.
      So data labels for the latest 3 data points in the visualisation based on your selection.

      Please see screendump with data from your model where I have put in "Measure" as custom data label.

      Br,
      Thomas

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi thod 

        You can refer to the following measure

        Measure 4 = var _topyear=TOPN(3,SUMMARIZE(ALLSELECTED('Table'[Year]),[Year]),[Year],DESC)
var b=CONVERT(SELECTEDVALUE('Table'[Month]),STRING)
var c=CONVERT(SELECTEDVALUE('Table'[Date]),STRING)
return IF(ISINSCOPE('Table'[Date].[Day]),IF(CONTAINSSTRING(SELECTEDVALUE('Table'[Max date]),c),SUM('Table'[Column1]),"NA"),IF(ISINSCOPE('Table'[Date].[Month]),IF(CONTAINSSTRING(SELECTEDVALUE('Table'[Max month]),b),SUM('Table'[Column1]),"NA"),IF(SELECTEDVALUE('Table'[Year]) in _topyear,SUM('Table'[Column1]),"NA")))

        Output

         

         

         

        Best Regards!

        Yolo Zhu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.