Obtain Latest Date out of 3 columns but ignore future date

[romoguy15]

Hello Everyone,

I have been working on a calculated column measure to extract the latest Store date based on Open, Remodel, and Relocation Dates. The problem is, some of the dates in each column might have a future date. I would like to pull in the latest of the three but ignore the future date. If you see in my snip below, the latest date is pulling 1/27/22 from remodel column. 

Sample data

https://1drv.ms/u/s!AqID1H0nHPOzhAhnRs-UyPdxAILB?e=0iCO2f

Thank you

[romoguy15]

@Anonymous  Thank you! This is exactly what I needed. 

[romoguy15]

Hi @Ashish_Mathur , thank you for the feedback. In this scenario, I do not want to unpivot the dataset. The store list is actually a very large store master list. So I only want one row for each store #. Each column for the open, relo, and remodel needs to be it's only column. I do have a date calendar table that I will be creating the relationship once I have the "Latest Date" column measure sorted out.

[Anonymous · Accepted solution]

Hi @romoguy15 ,

Try this calculated column

Latest Date =
VAR _if =
    IF (
        [Open] <= [Remodeled]
            && [Remodeled] <= TODAY (),
        [Remodeled],
        IF ( [Open] <= TODAY (), [Open], BLANK () )
    )
VAR _if2 =
    IF ( _if <= [Relocated], [Relocated], _if )
RETURN
    IF ( _if2 > TODAY (), _if, _if2 )

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Ideally you should unpivot your dataset and maintain only 1 column.  This way you will be able to create a relationship of the Date column with the Date column of the Calendar Table and also build a date slicer.

[romoguy15]

If it helps any, I've posted the sameple data below.
https://1drv.ms/u/s!AqID1H0nHPOzhAhnRs-UyPdxAILB?e=0iCO2f

[romoguy15]

@HotChilli so if I try plugging it in power query and using the DateTime.LocalNow() , how would I inster that into it?

= Table.AddColumn(#"Removed Columns", "Latest", each List.Max({[Date Open], [Remodel Date], [Relocation Date]}), type date)

[romoguy15]

@amitchandak I tried that measure in a column but am getting an error on the today that it is not a vaild table. I also want to note that the remodel, relocation, and open date columns are all part of the same table so I am not sure if Union will be needed.

[amitchandak]

@romoguy15 , A new measure like

maxx(filter(union(all(Table[Open]), all(Table[Remodel]), all(Table[Relocation Dates])), [Open] <=Today), [Open])

[HotChilli]

You add a condition to check if date is before today (in DAX it's TODAY(), in Power Query

DateTime.LocalNow()  )