Get Max Date of 2 Columns

[VWRdata]

I'm trying to get the Max date for each store and UPC. I managed to get the max date by store with: Measure 2 = CALCULATE ( MAX ( Distribution[Call Date] ), ALLEXCEPT ( Distribution, distribution[StoreNameEN] ) ) but then I need it by UPC as well.  

So, the Max date for IGA#0410-BEAUPORT / 626027730029 would be 04/06/2018 and for IGA#0410-BEAUPORT / 626027730000 would be 07/05/2018. 

What is the formula to do this?

StoreNameEN	UPCUnit	Call Date
IGA#0410-BEAUPORT	626027730029	04/06/2018
IGA#0410-BEAUPORT	626027730029	31/05/2018
IGA#0410-BEAUPORT	626027730000	02/05/2018
IGA#0410-BEAUPORT	626027730000	07/05/2018
IGA#0410-BEAUPORT	626027730029	29/05/2018
SUPERC#5941-VAUDREUIL	626027730029	18/06/2018
IGA#8507-CONTRECOEUR	626027730029	22/05/2018
IGA#0294-MONTREAL	626027730029	04/06/2018
METRO#22635-QUÉBEC	626027730029	14/06/2018
IGA#8096-DUBERGER	626027730029	17/05/2018
METRO#1022-REPENTIGNY	626027730029	07/05/2018

[v-piga-msft · Accepted solution]

Hi @VWRdata,

You also could have a try with the formula below.

Measure =
CALCULATE (
    MAX ( 'Table1'[Call Date] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[StoreNameEN] = MAX ( 'Table1'[StoreNameEN] )
            && 'Table1'[UPCUnit] = MAX ( 'Table1'[UPCUnit] )
    )
)

If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[Anonymous]

Hi

You can give a try to this. However, would like to learn from @Greg_Deckler that whether this is efficient approach.

I know this is calculated column, not a measure though ( known dis advantage 🙂 )

CALCULATE ( MAX(Table1[Call_date]), 
            FILTER( Table1,Table1[Store_Name] = EARLIER(Table1[Store_Name])),
            FILTER( Table1,Table1[UPC] = EARLIER(Table1[UPC]))
           )

Thanks

Raj

[Greg_Deckler]

If you really need a true mult-column aggregation, you can use my Quick Measure "MC Aggregations". 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-...

[Seward12533]

The simple measure

Max_Date  = MAX(Distribution[Call Date]) 

should work and then when you use it in a chart/table powerbi will apply the right filter context when calculating.