cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ISUGraber Member
Member

Rolling Termination sum on a rolling Trend Line

I've found several articles that touch on different ways to do this but I was curious.  

 

Is there a way we can take the steps that this SQLJason has done (which is great).

 

If you want an example dataset the below link has one you can download that is just like mine.  Except I want to do a rolling sum on the rolling graph he has.

 

Rolling N Months Sums

 

But then do a Rolling on top of the rolling he is doing.  So basically  I want to have rolling 3 month Sales that Rolls 12 months.   So my trend line would have:

 

Month  Rolling 3 Month Sum Sales    Sales

Dec            3000                  1000
 
Nov            3200                  500

Oct             2700                 1500

Sept            2750                  1200

etc....

 

 

This is my code I would like to show the Rolling Sum of Sales for each of the above months in a single Trend Line.  However, when I put this measure into a graph it simply shows the last 3 months of Sales broken out.

 

Rolling Sales 3 Month Total = 
CALCULATE (
    'Measures Fact'[Sales],
    DATESINPERIOD (
        'DIMCalendar'[Date],
        LASTDATE (  ('DIMCalendar'[Date] ) ),
        -3,
        MONTH
    )
)

 

 

This is the code that works in simply showing the last "N" months of Sales (from the above link):

 

Sales(last n months) = 
CALCULATE (
    SUM ( FactSales[Sales] ),
    DATESINPERIOD ( 'DIMCalendar'[Date], MAX ( 'DIMCalendar'[Date] ), - [N Value], MONTH )
)

 

 

 

1 REPLY 1
v-yulgu-msft Super Contributor
Super Contributor

Re: Rolling Termination sum on a rolling Trend Line

Hi @ISUGraber ,

 

Have you noticed that "It is important to know that putting the Month from the Date table will not work, so what we are going to do is create a month column in the Sales table and then use that as the axis for the bar chart.", which is mentioned in above link? 

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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: 37 members 952 guests
Please welcome our newest community members: