Get Max date for every delivery. Calculated column?

[rpinxt]

This is the situation:

A delivery with several log dates.

I linked log date to my date table to get it in periods.

Because there are 2 log dates (in this case. many times even more) this delivery will pop up in 2 periods.

I want it to only pop up in the latest period.

So I was thinking create a calculated column to get the max date for every delivery.

In this case both lines should get date 2-1-22.

That column I would then link to my date table instead of the column Log Date.

So in that way the delivery should only show up in 1 period.

Would this the best way?

And if yes how what that calculated column look?
Guess max([log date]) will not be enought because it has to reset at every delivery number.

[v-shex-msft · Accepted solution]

HI @rpinxt,

You can try to use the EARLIER function with iterator function MAXX to get the max date based on current field values and use it as formula conditions.

newColumn =
MAXX ( FILTER ( Table, [delivery] = EARLIER ( Table[delivery] ) ), [log date] )

EARLIER vs EARLIEST in DAX - Excelerator BI

Regards,

Xiaoxin Sheng

[rpinxt]

Thanks @Ashish_Mathur @v-shex-msft !

Both your solutions worked like a charm :

And if I then take out log date it consolidates to 1 line with only the 10 in period 2 as I wanted 😄

Thanks again guys! Very helpful

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula will work

Revised log date = calculate(max(Data[Log date]),filter(Data,Data[Delivery]=earlier(Data[Delivery])))

Hope this helps.

[v-shex-msft · Accepted solution]

HI @rpinxt,

You can try to use the EARLIER function with iterator function MAXX to get the max date based on current field values and use it as formula conditions.

newColumn =
MAXX ( FILTER ( Table, [delivery] = EARLIER ( Table[delivery] ) ), [log date] )

EARLIER vs EARLIEST in DAX - Excelerator BI

Regards,

Xiaoxin Sheng