Trend of last 12 months

[Niraj_vora0106]

Hi community, 

I've a query which I'm trying to solve. 

Suppose I'm in April 2022 and I want trend of last 12 months,

ie : from April 2021 to March 2022. and I want it to be dynamic if I select May 2022 it should range from May 2021 to April 2022.

Please help!

[v-eqin-msft · Accepted solution]

Hi @Niraj_vora0106 ,

1. You could simply apply Relative Date slicer to filter values based on current day:

2. If you want to dynamically change visuals based on date selection, please firtstly add a Date table for slicer:

For Slicer = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Then create a flag measure for visual-filter:

Flag = 
var _diff=DATEDIFF(MAX('Table'[Date]),MAX('For Slicer'[Date]),MONTH)
return IF( _diff>0 && _diff<=12,1,0)

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.

[PC2790]

Hey 

You can use DATESINPERIOD DAX function here.

You can do something like this

TotalSalesinPeriod = CALCULATE([Total Sales],DATESINPERIOD(DatesTable[Date],MIN(DatesTable[Date]),-12,MONTH))​

I found a very interesting article that you can refer:

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...