Forum Discussion
Add sales based on dynamic year selection
Hello,
Can i ask someone to help out on this requirements.
| Year | Sales | Year Selection | ||
| 2010 | 100 | If year 2013 is selected in filter | ||
| 2011 | 200 | 2013 | then add year 2013 , year 2012, and year 2011 sales | |
| 2012 | 300 | Result | 900 | |
| 2013 | 400 | |||
| 2014 | 500 | If year 2020 is selected in filter | ||
| 2015 | 600 | 2020 | then add year 2020, 2019, and 2018 sales | |
| 2016 | 700 | Result | 3100 | |
| 2017 | 800 | |||
| 2018 | 900 | |||
| 2019 | 1000 | |||
| 2020 | 1200 | |||
| 2021 | 1300 |
thanks
CJ_96601 , Create a separate year table( Say Date) and join on year , and try a formula like
rolling = CALCULATE(sum('Table'[sales]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-3 && 'Date'[Year]<=max('Date'[Year])) )
in case you need a trend display then do join the year table
measure =
var _max = maxx(allselected('Date'), 'Date'[Year])
return
calculate(sum('Table'[sales]), filter(Table, Table[Year] >= _max -3 && Table[Year] <= _max))Use -2, in place -3
3 Replies
CJ_96601 , Create a separate year table( Say Date) and join on year , and try a formula like
rolling = CALCULATE(sum('Table'[sales]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-3 && 'Date'[Year]<=max('Date'[Year])) )
in case you need a trend display then do join the year table
measure =
var _max = maxx(allselected('Date'), 'Date'[Year])
return
calculate(sum('Table'[sales]), filter(Table, Table[Year] >= _max -3 && Table[Year] <= _max))Use -2, in place -3
Thanks...it worked
Hi CJ_96601 ，
Would you please try the following measure:
Measure = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Year]<=MAX('Table'[Year])&&'Table'[Year]>MAX('Table'[Year])-3))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
