Power BI Rolling 6 Month Sum

0
votes

I am trying to calculate a 6 month rolling sum of the 'Booked' column pictured below. I am still learning Power BI and can't seem to figure out a way to do this. Basically, the perfect solution would create another column where each month had a total for the previous 6 months of booked dollars. So for example, 2017-06 would be (1,091,031 + 1,108,326 + 135,000 + 125,327 + 236,515 + 101,296) = 2,797,495

Thank you!!!!

Example Chart

1
powerbidax
Try starting here: daxpatterns.com/cumulative-total – Alexis Olson

1 Answers

2
votes

Let's assume your table name is "Sales".

First, create several base measures:

[Total Booked Sales]:= SUM(Sales[Booked])

[Period End]:=LASTDATE('Sales'[Date])

[Period Start]:= FIRSTDATE( DATESINPERIOD('Sales'[Date], [Period End], -6, MONTH))

Then, calculate rolling 6-months sales:

Sales 6 Months:=CALCULATE( [Total Booked Sales],
DATESBETWEEN ( 'Sales'[Date], [Period Start], [Period End] ))

Side notes:

  • These are Measures, not calculated columns.
  • Instead of using "date" column in your sales table, you should add Calendar table to your data model (Calendar tables )