Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Prabha45
Helper III
Helper III

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 2022.
Is it possible to have a 12 months flag on complete page. I have a table visual with columns from data source, no dax is involved in this table and a couple of cards having count of a column.
I've been trying a lot of solutions but nothing worked out. Would appreciate if anyone can help in this.

4 REPLIES 4
negi007
Community Champion
Community Champion

@Prabha45 you can create a calc column in your main date table. Below calc will calc months from today and you can apply filter accordingly in your data to show months greater than -12.

 

MonthsFromToday = DATEDIFF(TODAY(), 'Calendar'[Date],MONTH)

 

negi007_0-1672043346122.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

amitchandak
Super User
Super User

@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

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.

@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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.