Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |