Sorting the share holder name which maximum date (3/11/2022) are shows the higher shareholding value

[Anonymous]

HI, 

can someone advice how can i get the result...  I want to sort the share holder name which maximum date (3/11/2022) are showing the higher shareholding value than minimum date (2/9/2022). if let say the DAX code requires hope some one can help.Thank You

[v-yiruan-msft]

Hi @Anonymous ,

Do you want to get the rows which the value with maximum date is higher than the one on minimum date, right? If yes, I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure as below:

Flag = 
VAR _selholder =
    SELECTEDVALUE ( 'Table'[Holder_Name] )
VAR _seldate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _mindate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Holder_Name] = _selholder )
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Holder_Name] = _selholder )
    )
VAR _mindvalue =
    CALCULATE (
        SUM ( 'Table'[Sum Shareholding] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Holder_Name] = _selholder
                && 'Table'[Date] = _mindate
        )
    )
VAR _maxdvalue =
    CALCULATE (
        SUM ( 'Table'[Sum Shareholding] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Holder_Name] = _selholder
                && 'Table'[Date] = _maxdate
        )
    )
RETURN
    IF ( _maxdvalue >= _mindvalue && _seldate = _maxdate, 1, 0 )

2. Create a table visual and apply the visual-level filter with the condition(Flag is 1)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[FreemanZ · Accepted solution]

You can create a new table with the code below:

MaxTable =

ADDCOLUMNS(

    VALUES(Data[Holder_Name]),

    "Date",

    VAR CurrentName = Data[Holder_Name]

    VAR MaxAmount = MAXX ( FILTER (Data,  Data[Holder_Name] = CurrentName), Data[Sum Shareholding])

    VAR MaxDate =

        CALCULATETABLE (

            VALUES(Data[Date]),

            Data[Sum Shareholding] = MaxAmount

        )

    RETURN MaxDate,

    "Amount",

    VAR CurrentName = Data[Holder_Name]

    VAR MaxAmount = MAXX ( FILTER (Data,  Data[Holder_Name] = CurrentName), Data[Sum Shareholding])

    RETURN MaxAmount

)

[Anonymous]

the result showing error 😅. btw TQVM

[daXtreme]

// measure 1

[Date with Highest Value] =
// Works even if there are many
// companies visible. It'll then
// return the date for which the
// value is the highest among all
// the values visible for the selected
// companies. If one company is visible,
// you get what you've asked for. If
// you don't want this behaviour, you can
// always wrap this into an IF conditional
// logic.
var Output = 
    MAXX(
        topn(
            1,
            T,
            // The combination of these three fields
            // should be unique. Then this function
            // will return at most 1 row.
            T[Sum Shareholding],
            desc,
            T[Date],
            desc,
            T[Holder_Name],
            asc
        ),
        T[Date]
    )
return
    Output
    
// measure 2

[Total Sum Shareholding] =
var Output = 
    SUMX(
        topn(
            1,
            T,
            T[Sum Shareholding],
            desc,
            T[Date],
            desc,
            T[Holder_Name],
            asc
        ),
        T[Sum Shareholding]
    )
return
    Output