Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have tried so many solutions. But couldn't get what I am looking for. In the below sample table, I need the Previous Latest Date column as a result as a part of the power query table itself.
Any help is much appreciated.
CIF Date Previous Latest Date
0123456789 20/07/2020 03/05/2020
0123456789 03/05/2020 02/02/2020
0123456789 02/02/2020 01/01/2020
0123456789 01/01/2020 01/01/2020
Regards, Ramnath
Solved! Go to Solution.
Hi. This is not the best place to do it if you have a database as source because it has to query itself. Let's see. Add a custom column and insert the statement after "LastD", the final code should look like this:
= Table.AddColumn(#"Last Step", "LastD",
(Earlier) =>
Table.Max(
Table.SelectRows(
Table.SelectColumns(#"Changed Type", {"Date", "CIF"})
, each Earlier[Date] > [Date] and [CIF] = Earlier[CIF]
)
, "Date", Earlier
)[Date]
)
Hope this helps,
Happy to help!
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
I am not clear if and how your problem was solved.
but in any case try if this is right for you
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bczBCcAwDEPRXXwOWJLTaUz2X6MtPgSHXD9PyjSSNkzhoAuCrZEm6Y+zxZKU4zklo8WS3xbzlFCLJQOXT3HH9QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CIF = _t, DATE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CIF", Int64.Type}, {"DATE", type date}}),
maxSmaller = (lst, el)=>
let
ls=List.Sort(lst)
in ls{List.Max({List.PositionOf(ls,el)-1,0})},
ms = (tab)=> Table.AddColumn(tab, "maxSmaller", each maxSmaller(tab[DATE], _[DATE])),
#"Grouped Rows" = Table.Group(#"Changed Type", {"CIF"}, {{"ms", each ms(_)}}),
#"Expanded ms" = Table.ExpandTableColumn(#"Grouped Rows", "ms", {"DATE", "maxSmaller"}, {"DATE", "maxSmaller"})
in
#"Expanded ms"
Hi. This is not the best place to do it if you have a database as source because it has to query itself. Let's see. Add a custom column and insert the statement after "LastD", the final code should look like this:
= Table.AddColumn(#"Last Step", "LastD",
(Earlier) =>
Table.Max(
Table.SelectRows(
Table.SelectColumns(#"Changed Type", {"Date", "CIF"})
, each Earlier[Date] > [Date] and [CIF] = Earlier[CIF]
)
, "Date", Earlier
)[Date]
)
Hope this helps,
Happy to help!
This is almost close to what I want. But the only problem is it's giving me 4x4 =16 combinations. Instead, I want only the previous max date against all the CIFs. Is it possible for you to help?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thank you Allison for the prompt response. I went through the link. But it doesn't help. I am looking for M Query formula to reference the next latest date for each of the CIF numbers. I have a database of 1 million records.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
hi @Ramnath
"I am looking for M Query formula to reference the next latest date for each of the CIF numbers. I have a database of 1 million records."
the "next latest date" is "local" by CIF?
or it is global?
The database is ordered by some column?
Yes. It's Local. Just to give you a background, it's the customer application data. So the customer can apply several times through different channels and in different months. We need to know when he has last applied and see whether it's within 30 days. If so, I need to make the current record inactive and make the previous record active.
I am not clear if and how your problem was solved.
but in any case try if this is right for you
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bczBCcAwDEPRXXwOWJLTaUz2X6MtPgSHXD9PyjSSNkzhoAuCrZEm6Y+zxZKU4zklo8WS3xbzlFCLJQOXT3HH9QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CIF = _t, DATE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CIF", Int64.Type}, {"DATE", type date}}),
maxSmaller = (lst, el)=>
let
ls=List.Sort(lst)
in ls{List.Max({List.PositionOf(ls,el)-1,0})},
ms = (tab)=> Table.AddColumn(tab, "maxSmaller", each maxSmaller(tab[DATE], _[DATE])),
#"Grouped Rows" = Table.Group(#"Changed Type", {"CIF"}, {{"ms", each ms(_)}}),
#"Expanded ms" = Table.ExpandTableColumn(#"Grouped Rows", "ms", {"DATE", "maxSmaller"}, {"DATE", "maxSmaller"})
in
#"Expanded ms"