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

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!

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

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.

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 339 members 3,901 guests
Recent signins: