Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |