Filtering Max Date with AllEXECPT in M Language

[Wedding55]

Hello everyone,

I have the following Dax formula to create a new column in the Power BI Report.. However, I would like to create this step in the Query Editor. But im not sure how to do this. I created a normal max Funktion but i need the ALLEEXCEPT function for "Date_per month" in the M Language solution.

Dax Formula:  Max_Date = CALCULATE(MAX(Date[LastDate]), ALLEXCEPT(Date, Date[Date_per_month]))

M Language: List.Max(#"Changed Type2"[LastDate]) 

Do you have any ideas how to do this?

Best Regards

[Wedding55 · Accepted solution]

Hey, thanks for your answer. I solved it with ja group by function and joined the data later:)

[Anonymous]

Hi,

Thanks for the solution @BITomS  provided, and i want to offer some more information for user to refet to.

hello @Wedding55  , you can create a custom column and try the following code.

let a=[Date_per_month]
in List.Max(Table.SelectRows(#"Changed Type2",each[Date_per_month]=a)[LastDate])

Best Regards!

Yolo Zhu

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

[BITomS]

Hi @Wedding55 ,

It sounds like the Group By function could help here (available in the Transform tab)?

Group By Date_per_month and use the MAX aggregation?