cancel
Showing results for
Did you mean:
Frequent 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

#### CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors