Forum Discussion
Get Max date for every delivery. Calculated column?
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.
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
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.
3 Replies
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
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.
Thanks Ashish_Mathur Anonymous !
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
