Forum Discussion

Prabha45's avatar
Prabha45
Icon for Helper III rankHelper III
3 years ago

Rolling 12 months filter

Hi, I want to filter my data on the last 12 months period. For instance if I select year as 2022 and month as Jan, I want the data to be filtered on the last 12 months such as from Jan 2021 till Jan ...
Need Help
Tips and Tricks
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

Prabha45 , if you want to show rolled up data of 12 month you can have measure like this with help from date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

 

But if you want to show 12 month trend on the axis, you need a slicer on an independent date table

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

 

Rolling Months Formula: https://youtu.be/GS5O4G81fww

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

Prabha45's avatar
Prabha45
Icon for Helper III rankHelper III
3 years ago

Thanks for you reply. But, there is no need of calculation of columns. Table visual is having only the columns (Name, description, date, department) coming from data source. No measures are used in this table.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Prabha45 , Not clear on "no need of calculation of columns" and "No measure"

    I suggested measures. Would need more information, what is needed


    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.