DAX. Find value based on rankx

[gkir]

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 is that the median which I need is calculated not arithmetically, but negative values should be the worst, then the large positives and the best values are small positives.

So, first of all, I need to rank values during 3 years period for each item and find the middle number.

I tried to use RANKX formula but I have no idea where should I put the restriction about 3 years and something more is wrong because it's not calculating correctly even for all period:

[PaulDBrown]

Thanks. I take it that if there are more negative values, you want them ranked in descending order also?

-1

-2

9

8

6

3

Do you need it down to date level? So it would be the median for all dates in the last 3 years <= to the current date in the visual?

[gkir]

Thank you for your replies!

I need negative numbers ranked in ascending order. For example: 
-15
-2
9
8
6
3

Median: 8,5.

Yes, I need for every day calculate median for all dates in the last 3 years <= to the current date.
For example for July 18th, 2022 we need rank numbers from July 19th, 2019 to July 18th, 2022.
For July 15th, 2022 rank all numbers from July15th, 2019 to  July 15th, 2022.
And all these medians should be calculated for each item individually.

[PaulDBrown]

Ok, this is turning out to be quite a challenge for my scope of DAX. Here is the progress I've made so far.

To test the measures, I've opted for creating a filter date to see the physical rendering of each measure. This means there are some filter expressions in the measures simply to get rid of blank rows.

So here goes

First, the model (including a separate date table to limit the rows in the visual. For this exercize I'm looking at a 1 year range:

Now the measures step by step:

Sum Value = SUM(FactTable[Value])

Ascending rank for rows with negative values:

Rank -ves =
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] ) )
    )
VAR _Rows =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Date Table'[Date] ),
            FILTER (
                'Date Table',
                'Date Table'[Date] > _mindate
                    && 'Date Table'[Date] <= MAX ( 'Date Sel'[Sel Date] )
            )
        )
    )
RETURN
    IF (
        AND ( _Rows >= 1, [Sum Value] < 0 ),
        RANKX ( _DatesTable, [Sum Value],, ASC, DENSE )
    )

Number of rows with negative values:

MAX _ve Rows = 
MAXX(ALL('Date Table'[Date]), [Rank -ves])

Descending Rank of positive values

Rank +ves =
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] ) )
    )
VAR _Rows =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Date Table'[Date] ),
            FILTER (
                'Date Table',
                'Date Table'[Date] > _mindate
                    && 'Date Table'[Date] <= MAX ( 'Date Sel'[Sel Date] )
            )
        )
    )
RETURN
    IF (
        AND ( _Rows >= 1, [Sum Value] > 0 ),
        RANKX ( _DatesTable, [Sum Value],, DESC, DENSE ) + [MAX _ve Rows]
    )

Final Rank of all rows by Item

All Rank Vals = 
[Rank -ves] + [Rank +ves]

Median calculation

MEDIAN =
VAR _Table =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( FactTable, NOT ( ISBLANK ( ( [All Rank Vals] ) ) ) ),
            'Dim Item'[dItem],
            'Date Table'[Date]
        ),
        "@RANK", [All Rank Vals]
    )
VAR _Median =
    ROUND ( MEDIANX ( _Table, [@RANK] ), 0 )
RETURN
    _Median

FInal Value based on Median:

Final Median Value =
VAR _FinalMed =
    ROUND ( CALCULATE ( [MEDIAN], REMOVEFILTERS ( 'Date Table'[Date] ) ), 0 )
RETURN
    AVERAGEX (
        ALLSELECTED ( 'Date Table'[Date] ),
        CALCULATE ( IF ( [All Rank Vals] = _FinalMed, [Sum Value] ) )
    )

To get:

I've attached the sample PBIX file

Apologies for the delay, but as I say, it's quite a challenge. I'll keep at it, but can't promise anythig at this stage...

[gkir]

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]

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])))

[PaulDBrown]

Thanks for the data sample. Can you please explain how you wish to calculate the median? I don't quite understand the statement reading

"The problem is that the median which I need is calculated not arithmetically, but negative values should be the worst, then the large positives and the best values are small positives"

Maybe if you provide a practical example?

[gkir]

For example:
Item A has values:
-2
3
6
8
9

Arithmetical median would be: 6.

but we want to rank like this:
-2
9
8
6
3

So our median is 8.

[PaulDBrown]

Can you please provide sample non-confidential data? (Not as an image)

[gkir]

https://bun.filedit.ch/RNVjkRlEyyBoWmYvuMy.xlsx