Rolling 5years - DAX measure

[OlaSa]

I have a table (matrix) with...
RegistrationYear (2001, 2002...2025)
Brand (MB, VW, ...)
NoOfCarsSold (quantity)


How can I calculate the 'Rolling 5years park' by Brand and Year ?

Rolling 5years park:
Rolling5-Yr2005 = Sum(2001, 2002, 2003, 2004, 2005) by brand
Rolling5-Yr2006 = Sum(2002..2006) ...
etc

Yr2001 to Yr2004 will be blank since it's less then 5 years.

 

[Matt Allington]

You will need to unpivot your day
te so you have a column called year and another called cars sold.

Read up on my article here
DAX Time Intelligence Explained - Excelerator BI

 

[OlaSa]

Hello Matt,

Actually, it's already a table, with 3 Columns: Brand, Year, NoOfCarsSold
I had a look at your site but couldn't figure out how to apply the formulas.


This is as far as I could come:

Test:=IF([Check];CALCULATE(SUM([NoOfCarsSold]);
ALLEXCEPT(Table1;Table1[Brand]);
AND(Table1[Year]<=2025;Table1[Year]>=2007));0)

Check:=AND(
SUM(Table1[Year])<=[Year-End];
SUM(Table1[Year])>=([Year-Start]+9))

 

[OlaSa]

If it simplifies: Take away the Brand-column. That leaves two columns; [Year] and [NoOfCarsSold].

 

[Matt Allington]

Is this still an issue? If so and if you post a sample workbook showing what you want then I will help you out.

 

[citizenbh]

Something like this:
(I have not worked this case for less than 5 years)
Sory, change F2

[\IMG]

Link File:https://www.dropbox.com/s/5er6cgts362dlsw/Rolling5years.xlsx?dl=0

 

[Matt Allington]

Here you go.

https://www.dropbox.com/s/v8v439xs6tgqy2v/Rolling5years.xlsx?dl=1