cancel
Showing results for
Did you mean:
Frequent Visitor

## Rolling 3 months

Hi there, I'm struggling to add a certain condition in power bi that returns financial fegures for only a certain months. I want to get the financial graphs for the last 3 months from the system date everytime I log in to my dashboard to check. E.g

System date = 1/1/2016 . Dashboard to show (October, November, December)

System date = 1/2/2016 . Dashboard to show (November, December, January) and so on.

2 REPLIES 2
Highlighted
Super User

## Re: Rolling 3 months

You should be able to use the rolling totals pattern or other technique from here:

http://www.daxpatterns.com/cumulative-total/

You could create a column in your data that essentially determines whether it is in the last 30 days, equals 1, between 30 and 60, 2 and between 60 and 90, 3. Then you could set your report filters for only values in that column that equal 1, 2 or 3. You would use the TODAY function like:

Last Three Months = IF(TODAY()-[Date]<=30,1,IF(TODAY()-[Date]<=60,2,IF(TODAY()-[Date]<=90,3,0)))

Then just filter out the 0's in the report or page filter area.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Member

## Re: Rolling 3 months

You could build a date table and enrich it with some calculated column.

For example :

 [Relative Date Offset] = INT([Date] - TODAY())[Relative Date] = IF([Relative Date Offset]=0; "J "; "J " & IF([Relative Date Offset]>0; "+"; "") & [Relative Date Offset])[Relative Month Offset] = ((12 * YEAR([Date])) +  MONTH([Date])) - ((12 * YEAR(TODAY())) +  MONTH(TODAY()))[Relative Month] = IF([Relative Month Offset]=0; "M "; "M " & IF([Relative Month Offset]>0; "+"; "") & [Relative Month Offset])[Relative Week Offset] = INT(([Date] - (TODAY() - WEEKDAY(TODAY();2)+1))/7)[Relative Week] = IF([Relative Week Offset]=0; "W "; "W " & IF([Relative Week Offset]>0; "+"; "") & [Relative Week Offset])

You can modify this for your needs.

And use them as filter, slicer or just axis in your reports.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 394 members 3,456 guests
Recent signins: