cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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.


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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 303 members 3,101 guests
Please welcome our newest community members: