cancel
Showing results for
Did you mean:
Regular Visitor

## Previous YEARS TDS as % of Sales

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

1 ACCEPTED SOLUTION
Super User

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.

Super User

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.

Announcements