Reply
Frequent Visitor
Posts: 2
Registered: ‎11-19-2018
Accepted Solution

Rolling LTM by month - all 12 months in same visual

I'm new to Power BI and hoping to get some guidance on a visual. I'm having difficulty creating a rolling month-by-month look at LTM Revenue. 

 

For eaxample, if it's November 1, 2018 I'd like to see LTM data as of October 2018, September 2018, August 2018,...,December 2017, and November 2017. 

 

I know I can create a slicer to see each month's LTM Revenue individually, but ideally I'd like to see all 12 months LTM Revenue in a single bar chart.

 

Is this even possible?


Accepted Solutions
Highlighted
Established Member
Posts: 177
Registered: ‎04-27-2018

Re: Rolling LTM by month - all 12 months in same visual

@mpranev

 

Wow I just created a video on this exact topic this morning... check it out on my YouTube channel!

 

https://www.youtube.com/watch?v=duMSovyosXE

 

Hope this helps,

Parker

View solution in original post


All Replies
Community Support Team
Posts: 1,648
Registered: ‎07-10-2018

Re: Rolling LTM by month - all 12 months in same visual

Hi @mpranev,

 

I made one sample for your referece, If it doesn't meet your requirement, kindly share your sample data and excepted result to me. You can upload the files to onedrive and share the link here.

 

Here are the steps.

 

1. Create a date table.

 

Table2 = CALENDAR(DATE(2017,11,01),DATE(2018,11,30))

2. Create a measure as below.

 

Measure = var su = SUM('Table'[value])
return
IF(ISBLANK(su),BLANK(),CALCULATE(SUM('Table'[value]),DATESINPERIOD(Table2[Date],LASTDATE('Table2'[Date]),-11,MONTH)))

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Frequent Visitor
Posts: 2
Registered: ‎11-19-2018

Re: Rolling LTM by month - all 12 months in same visual

Frank,

 

I really appreciate you taking the time to help answer my question.  I've provided sample data to help illustrate.

 

I'm looking to get a rolling sum of values based on the Date Closed.  Under the "Desired Output" table you can see the fees are pullind data for 12 months ending.  Hopefully that makes sense.

 

Capture2.PNG

 

 

Highlighted
Established Member
Posts: 177
Registered: ‎04-27-2018

Re: Rolling LTM by month - all 12 months in same visual

@mpranev

 

Wow I just created a video on this exact topic this morning... check it out on my YouTube channel!

 

https://www.youtube.com/watch?v=duMSovyosXE

 

Hope this helps,

Parker