Show N last periods in line chart

[Noeleke1301]

Hi all,

I want to dynamically show the N last periods in a line chart. I've read the SQLBI article, but that works with months and the DATESINPERIOD formule, which I cannot use because I use custom ISO periods.

Please help, I'm trying to use an offset column in my date table, which is set to 0 on the current period and counts back in the past. So 3 periods ago has offset -3

My current formula:

On time performance trend =

VAR _LastPeriodWithData = CALCULATE( MAX( dimDate[Period Offset] ) , FILTER( dimDate , dimDate[Date] <= MAX( trips[Date] ) ) )

return

CALCULATE( [ON TIME PERFORMANCE] , FILTER( dimDate , dimDate[Period Offset] >=  _LastPeriodWithData -3 && dimDate[Period Offset] <=  _LastPeriodWithData ) )

But this still shows everything in the chart, not just the last 3 periods. How do I correct this?

There is a single relationship between dimDate[Date] and trips[Date]

[amitchandak]

@Chaithanya6 , refer if these two from guyinacube can help

https://www.youtube.com/watch?v=EZifdFfjTNg

https://www.youtube.com/watch?v=v79MjJZoIZg

[amitchandak]

@Noeleke1301 , Try with this one change in var

VAR _LastPeriodWithData = CALCULATE( MAX( dimDate[Period Offset] ) , FILTER( allselected(dimDate) , dimDate[Date] <= MAX( trips[Date] ) ) )