Display latest 3 data points in data label

[thod]

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

[v-xinruzhu-msft]

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]

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

[v-xinruzhu-msft · Accepted solution]

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.