Dax Calculation

[Diptarup]

I am new to the PowerBi and would require assistance to solve a problem. I want to get the value of the product A & B based on the second highest year. As shown in the below table ,I want to have a formula which will give me the value of 17 for product A in 2019 & 16 for product B, as 2019 is the 2nd highest year in the table.

Please note that Year is not fixed and there are instance for other products werein the year may starts from 2018.   

Year	Product	Activity
2020	A	15
2019	A	17
2018	A	13
2017	A	12
2020	B	19
2019	B	16
2018	B	21
2017	B	15

[v-henryk-mstf]

Hi @Diptarup ,

I agree with @Jihwan_Kim  solution. But I also did the following tests, hoping to expand your ideas: 

M =
VAR a =
    RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[Year] ) ),, DESC, DENSE )
RETURN
    CALCULATE (
        SUM ( 'Table'[Activity] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Year] ),
            a = 2
                && 'Table'[Column] = MAX ( 'Table'[Column] )
        )
    )

Here is the sample pbix file.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Diptarup]

Can you please share the calculation for the col "Column" as the pbix file that you have acttcahed i am unable to open it due to some issue 

[v-henryk-mstf]

Hi @Diptarup ,

The calculated column is below:

Column = IF('Table'[Product]="A",1,2)

Best regards,

Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @Diptarup 

Please check the below picture and the calculated measure, whether it is what you are looking for.

Ranktwo Activity =
IF (
RANKX (
ALLSELECTED ( Products[Year] ),
CALCULATE ( MAX ( Products[Year] ) ),
,
DESC
) = 2,
SUM ( Products[Activity] ),
BLANK ()
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Diptarup]

Hi Jihwan,

I have tried the option that you have said, but not getting the desired result. I am re-phrasing the question, hoping to be clear...

I need to show the lowest value of an activity based on the most recent assesment. Please refer to the below table.

Year             Activity     Value

1/1/2020          A             3

1/1/2018           A             5

1/1/2017          A             2

1/1/2021           B             1

1/1/2021           C            15

1/1/2020            C            2

1/1/2019           C            6

1/1/ 2018         D             2

1/1/2017          D            16

1/1/2016          D              1

1/1/2017          E              4

1/1/2021          f              19

1/1/2020         G               12

1/1/2021        H                  1

I am expecting the output for the lowest values as D -2 (2018), B - 1 (2021), A - 3 (2020) , E - 4 (2017), H - 1 (2021). Please remember the values should be based on the latest assessment for each activity.  Any help will be highly appreciated.

[selimovd]

Hey @Diptarup ,

check the following SQLBI article, there they are describing exactly the scenario you are dealing with:

Displaying Nth Element in DAX - SQLBI

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Diptarup]

Hi Selimovd,

Many thanks for your reply.

I have tried the option that you have said, but not getting the desired result. I am re-phrasing the question, hoping to be clear...

I need to show the lowest value of an activity based on the most recent assesment. Please refer to the below table.

Year             Activity     Value

1/1/2020          A             3

1/1/2018           A             5

1/1/2017          A             2

1/1/2021           B             1

1/1/2021           C            15

1/1/2020            C            2

1/1/2019           C            6

1/1/ 2018         D             2

1/1/2017          D            16

1/1/2016          D              1

1/1/2017          E              4

1/1/2021          f              19

1/1/2020         G               12

1/1/2021        H                  1

I am expecting the output for the lowest values as D -2 (2018), B - 1 (2021), A - 3 (2020) , E - 4 (2017), H - 1 (2021). Please remember the values should be based on the latest assessment for each activity.  Any help will be highly appreciated.