cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate III
Advocate III

Rolling Month and Quarter Calculation

Purpose: Calculate Rolling 12 months and Rolling Quarter based off todays date 

 

Background: I would like to display data only from the last 12 calendar months. Power BI offers this as a relative date filter, but I would like to create a measure called "Rolling 12 Months" that my user could use in a slice on the page. 

 

Does anyone know the DAX calculation to create a rolling 12 months based off today's date? 

 

I would also like to display data only from the current quarter, i.e. a Rolling Quarter measure. For example, if I use the Rolling Quarter as a filter all my data will only display data for October (since it's October 18th). 

 

Does anyone know the DAX calculation to create a Rolling Quarter based off today's date? 

 

Q1 - Jan, Feb, March

Q2 - April, May, June

Q3 - July, Aug, Sept

Q4 - Oct, Nov, Dec

 

1 ACCEPTED SOLUTION
Advocate III
Advocate III

I created  Current Quarter Flag which allows me to filter my page on the current quarter. My rolling quarters issue is solved. Breakdown below

 

Curent Year = YEAR(TODAY())

Quarter = ROUNDUP([MonthNumber]/3, 0)

Current Quarter = ROUNDUP(MONTH([Current Date])/3, 0) 

Current Quarter Flag = if([Current Quarter] = [Quarter] && 'Date Dimension'[Year] = [Curent Year],"Y","N")

 

 

CurrentQuarterFlag.png

 

 

View solution in original post

1 REPLY 1
Advocate III
Advocate III

I created  Current Quarter Flag which allows me to filter my page on the current quarter. My rolling quarters issue is solved. Breakdown below

 

Curent Year = YEAR(TODAY())

Quarter = ROUNDUP([MonthNumber]/3, 0)

Current Quarter = ROUNDUP(MONTH([Current Date])/3, 0) 

Current Quarter Flag = if([Current Quarter] = [Quarter] && 'Date Dimension'[Year] = [Curent Year],"Y","N")

 

 

CurrentQuarterFlag.png

 

 

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors