How to get Max value of a mesaure based on category

[vineeta_dahiya]

I need help to fix an issue I am facing.Can anyone help here pls 

1. I need to get the Maximum transaction Date for KEY column (this KEY willl be similar to the column we give in Group By clause.

2.  Pick the  Schedule_Date which have  Max Transaction_Date derived from Step1. 

3. Get the max Schedule_Date  for a KEY and display it in visual

Dataset view:  these columns and many more columns

Order_Nbr	Item	Schedule	KEY	Transaction_Date	Schedule_Date
100	01	01	100-01-01	24-May-21	27-May-21
100	01	01	100-01-01	23-May-21
100	01	01	100-01-01	24-May-21	28-May-21
100	01	02	100-01-02	15-Apr-21	15-Apr-21
100	01	03	100-01-03	20-Apr-21
100	01	01	100-01-03	25-Apr-21	28-Apr-21

Required Resultset : these columns and many more columns as well

Order_Nbr	Item	Schedule	Transaction_Date	FinalSchedule_Date
100	01	01	24-May-21	28-May-21
100	01	01	23-May-21	28-May-21
100	01	01	24-May-21	28-May-21
100	01	02	15-Apr-21	15-Apr-21
100	01	03	20-Apr-21	28-Apr-21
100	01	01	25-Apr-21	28-Apr-21

I tried this doing using Caluclated Column and used Earlier and facing MAJOR PERFORMANCE ISSUE. 

Now I am thinking of doing the same using a measure. 

I am able to reach till Step2 and not finding the way to implement 3rd step. I am using below code. 

FinalSchedule_Date = var A = CALCULATE(

MAX('MCOFF_Sales_Transaction'[TRANSACTION_DATE]),

FILTER( ALL(MCOFF_Sales_Transaction),

MAXX( FILTER( MCOFF_Sales_Transaction , EARLIER( MCOFF_Sales_Transaction[KEY] ) = MCOFF_Sales_Transaction[KEY]),'MCOFF_Sales_Transaction'[TRANSACTION_DATE] )

)

)

Var B = MAXX(MCOFF_Sales_Transaction ,MAX(MCOFF_Sales_Transaction[TRANSACTION_DATE]))

Var C = MAXX(MCOFF_Sales_Transaction ,MAX(MCOFF_Sales_Transaction[SCHEDULE_DATE]))

var D = If(A=B ,C)

Return D

------------------------------------------------------------------------------------------------------------------------

Thanks,

Vineeta

[parry2k · Accepted solution]

@vineeta_dahiya try this measure:

Max Schedule Date for the key = 
CALCULATE ( MAX ( yourTable[ScheduleKey] ), ALLEXCEPT ( yourTable, yourTable[Key] ) )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[v-shex-msft · Accepted solution]

HI @vineeta_dahiya,

You can try to use the following measure formula if it suitable for your requirement:

FinalSchedule_Date =
CALCULATE (
    MAX ( 'MCOFF_Sales_Transaction'[TRANSACTION_DATE] ),
    ALLSELECTED ( MCOFF_Sales_Transaction ),
    VALUES ( MCOFF_Sales_Transaction[KEY] )
)

Regards,
Xiaoxin Sheng

[parry2k · Accepted solution]

@vineeta_dahiya try this measure:

Max Schedule Date for the key = 
CALCULATE ( MAX ( yourTable[ScheduleKey] ), ALLEXCEPT ( yourTable, yourTable[Key] ) )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡