Get max date with conditions by Query

[Anonymous]

Hi,

I have a Calendar table and I am trying to get max date with conditions by Query.

How can I get 1/3/2021 (max date & holiday=1)?

Calendar table

date	holiday
1/1/2021	1
1/2/2021	1
1/3/2021	1
1/4/2021	0
1/5/2021	0

I have try this but those are returning  1/5/2021.

= List.Max(Calendar[date], each [holiday]= 1)

= List.Max(Calendar[date], each [holiday]= 0)

Any help would be greatly appreciated.

[wdx223_Daniel · Accepted solution]

=List.Max(Table.SelectRows(Calendar,each [holiday]=1)[date])

[Anonymous · Accepted solution]

You could also just use Table.Max, which lets you use two parameters, like

=Table.Max(PriorStepOrTableName, {"Date", "Holiday"})

--Nate

[wdx223_Daniel · Accepted solution]

=List.Max(Table.SelectRows(Calendar,each [holiday]=1)[date])

[Anonymous]

@wdx223_Daniel 
Thank you😊

It worked😀