cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ray Frequent Visitor
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
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


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

Proud to be a Datanaut!

fbrossard Member
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.

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 394 members 3,456 guests
Please welcome our newest community members: