cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Diptarup
Helper II
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.   

YearProductActivity
2020A15
2019A17
2018A13
2017A12
2020B19
2019B16
2018B21
2017B15
7 REPLIES 7
v-henryk-mstf
Community Support
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] )
        )
    )

 

 

v-henryk-mstf_3-1618456359560.png

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.

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 

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
Super User
Super User

Hi, @Diptarup 

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

 

Picture2.png

 

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/

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.


Go to My LinkedIn Page


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
Super User
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
 

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.