cancel
Showing results for
Did you mean:
Helper II

## Dax Calculation

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
7 REPLIES 7
Community Support

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.

Helper II

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

Community Support

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.

Super User

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.

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

Helper II

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.

Super User

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

Helper II

Hi Selimovd,

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.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors