Rolling 12 YY-Month

[achanikya]

Hi Team, 

i want to display sales amount for last 12 Completed month in Line Chart.

For Example:

If january 2022 once Came, that time line chart should show the data From Feb-2021 To Jan -2022. Only 12 MOnths

Please advice some measures  for displaying completed 12 Months of data.

[v-eqin-msft · Accepted solution]

Hi @achanikya ,

If the last 12 months are always based on current date, you could simply use Relative Date filter as shown below:

2/11/2021-1/10/2022

Or if you want to dynamically change it based on date selection in slicer, please firstly create a date table for slicer, and then create a measure for filter:

ForSlicer = VALUES('Table'[Date])

Measure = 
var _date= DATE(MAX('ForSlicer'[Date].[Year]), MAX('ForSlicer'[Date].[MonthNo]),1)
var _diff= DATEDIFF(MAX('Table'[Date]),_date,MONTH)
return IF(_diff>=0&& _diff<12,1,0)

2021/2/1-2022/1/10

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

[amitchandak]

@achanikya , if you have not selected a date then

//Last 12 based on today
new measure =
var _max = eomonth(today(),0)
var _min = eomonth(today(),-12)+1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Assumes you are using joined date table.

But if you filtered Jan 21 and then want 12 months you need an independent date table

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI