Forum Discussion

gkir's avatar
gkir
Icon for Advocate II rankAdvocate II
4 years ago

DAX. Find value based on rankx

Hello,   Can someone help me with Dax? I have a table with values. Each item every day has no more than 1 value:   I need to calculate rolling 3 year median for every item. The problem i...
Need Help
gkir's avatar
gkir
Icon for Advocate II rankAdvocate II
4 years ago

Thank you very much! 

True, it is very very challenging 😄 As I see, ranking is correct, but need to set 3 years period for ranking.
However, I'm very happy to get some progression! At least something what could help me to move on 🙂


 

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago

regarding the period, I just limitted to 1 year because it's easer to see what's going on. 

For 3 years, just change the period reference. In fact, there is a cleaner code for the period calculation. 

Use

VAR TESTDATES = DATESINPERIOD('Date Table'[Date], MAX('Date Table'[Date]), -3, YEAR)

instead of the much more convoluted...

VAR _mindate = CALCULATE(MIN('Date Sel'[Sel Date]), DATEADD('Date Sel'[Sel Date], -1, YEAR))
VAR _DatesTable = CALCULATETABLE(ALLSELECTED('Date Table'[Date]), DATESBETWEEN('Date Table'[Date], _mindate+1, MAX('Date Sel'[Sel Date])))