Annualised 12 Month Sum Rolling

[rhawlor]

Hi PowerBi'ers!!

Nearly Chrismas 🙂

I have one for you all, which im sure is simple, but I've been trying different solutions ive seen on here and youtube, but it doesnt calculate right. 

Scenario:  x1 calculation column IW-MFR basically sums "IW-Faulty" and divides by "Cumulative Shipment" for each row

Aim: Create another column with a sum for rolling 12 months (sum the current row and the previous 11 rows) - This can be in Powrquery or a DAX column.

I have this in excel which is simple and is literally Sum previous current and previous 11 months:

This would make my Christmas if this could be solved!!! 🙂

Cheers Guys!!

[rhawlor]

Can anyone help me please? 🙂

[Mahesh0016]

SUM 12month =
VAR Total =
CALCULATE (
[Total ListPrice],
DATESINPERIOD ( 'Date'[Date], [LastCurrentDate], -12, MONTH )
)
RETURN
Total

******************************************************

LastCurrentDate = MAX('Date'[Date])

******************************************************

Total ListPrice = SUM(TrainingSample2[ListPrice])

[rhawlor]

Hi Mahesh

I have tried applying this in M Code in PQ and in Dax Table column, but i get varying errors:

Can you explain a bit more please?

[Mahesh0016]

Hi @rhawlor ,
You can not use in M Code it is use only Power bi desktop DAX Measure.

[rhawlor]

Thanks, ok that will be why it isnt working, I need a column added to the data, can you still help? 🙂

[Mahesh0016]

calculated column is useful for you instead of add column in data.

[rhawlor]

Yes i have tried that in New Column in DAX, but it just throws an error similar to the baove ones.

[Mahesh0016]

You can create a calculated column: 

Column = CALCULATE(SUM('Table3'[Value]),FILTER('Table3','Table3'[Date]<=EARLIER('Table3'[Date])))

[rhawlor]

Hi Mahesh

Thanks for the above, how do i incorporate the above to use only the previous 12 rows?