Previous YEARS TDS as % of Sales

[Anonymous]

Hi, I am trying to achieve below and I am immensely thankful for your suggesstions and expertise.

Year	Returns	Sales	Returns as % of Past 3 yrs Sales	Comment
2018	13	14892		No need to show this as there is no data for prior years
2019	5	16034		No need to show this as there is no data for prior years
2020	8	9387	0.06%	Formula : SUM(8+5+13)/SUM(9387+16034+14892)
2021	10	22189	0.05%	Formula : SUM(10+8+5)/SUM(22189+9387+16034)
2022	3	3096	0.06%	Formula : SUM(3+10+8)/SUM(3096+22189+9387)

I want this to be dynamic so that the calculation automatically happens for future years.

Thanks,

BINama

[lbendlin · Accepted solution]

Thank you for providing the sample data. That helps a lot with proposing a potential solution. Here is a measure based version

3yr Ret % = 
var y = SELECTEDVALUE('Table'[Year])
var t = CALCULATETABLE(TOPN(3,'Table','Table'[Year],DESC),ALLSELECTED(),'Table'[Year]<=y)
return if(COUNTROWS(t)=3,divide(sumx(t,[Returns]),sumx(t,[Sales])))

Depending on your situation it could also be done as a calculated column.

See pbix attached.