lookup between two dates (true function)

Anonymous — Sat, 05 Oct 2024 07:02:17 GMT

in the attched excel there are two sheets (Activity code 2 & dates ) , activtity code 2 column i need to make a lookup function on true basis of dates id ,
as i used lookup function its showing like this, showing exact match , but i need like in excel we can used true /false , so here need to use true to show nearest dates id against each date.https://alfanargroup-my.sharepoint.com/:x:/g/personal/bilal_nawaz_alfanar_com/EZyYQnQmxYRAq8oAw4ZArK0BMygMO73QRJyOF7HICYu5fw?e=qenlvo

Re: lookup between two dates (true function)

Anonymous — Sat, 05 Oct 2024 07:21:45 GMT

like in excel if we used vlookup and its show two type of result , false/ true or approx match , here i need true or approx match .

Re: lookup between two dates (true function)

lbendlin — Sun, 06 Oct 2024 00:05:24 GMT

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vVnbcqM4EP0VVZ52q8KOuBoeCVYcHHNZwOOpmp0H4rCJNx6c9WW25u+3BTRYGI8Th+RBp4hS9DmI7tYR/vr1YiRR+eLyYpTl2TpdwlXIJMVQ4CJirn8dRA7zmJ+Q4JokN9OZPSFJMPMByK09jRNJIU7g+8xJ3MAnl2M2HNo35BLmksh2EnJnx2zi+ozEzg0bTicMAsvVUExpnOaSQhX94tvl1ws2lCgnZvnDIs+y9SJ/IH/tKFUMMsw2i4f8Q9QpOAbSMJtzdWqpzpOoyhWsV/PdOvue5VvyG/t3t3jmlxtU6qVbUJ4uN79/iFq1GrIujXdLrlYr1DoS1WDaWeWb7Xo33y5WH7N6WjUUBfTkjR43lKgO8y7oSZfLlAsi4WO6yT5Ell4Nqkv27qGRlcA6GTCfZJvtXro5q+/fFxuQCHMfos+ohiyL+kLQNyiT7p9sviXOcrXJVrvth4gaVEOxRFEjIKaHPYO28o2s/iZufr+DCagHMl9sfxKFbFckTvP0LluSqzgkV+n8afcMC57nWXHXJRln9/fpIwQzqwF9wkvXQp8o2U42it4lWdWQLUzvsjl4yPbq7tC7RJnWIIsdzOmgez8Zcg2HraDkO94L+lej1KAedICS7wUtoH9ZKgI1pGC+FQq/TqjOyu9fi4YA9S5oGfkHxa5xzUl6t3siwyh0ig2oBkvyVz+a25n/glLtCGggUBXrv1qbiAf8c+qGRcfD0rITFrnQ9yQSRoEzjYp+eDz8AEGlYngn4Eu7+LFYktlq/bQ5HsJEoLKY4n7MFS5hjdeLObwNlJjNH9O8mBFy/wSNVQMVaRJOk7A4cf1RnbeB57lxDF0eJo/GVCiCbIrGIWDtpJOCOus6AskI7dd+O0z+UCxOcZv9JENofOUjlnPt1L1O77Nl+khkmZKnHyTe3cH6VA1KUWrQJPt5bycIw1nFgYrDdfacrtvLeganWoOGLhXTeVRx/jqnz+DUEMDNXWd3+xkfNM+J+8u5LDoCtaQ4e95PfP+PjnhvWkYDAYpYIHP9uCI7UgpnkA1qMMUOljisIjtZLWfQmjVoYgmETtBKz6KgCLbxM7isGlqlAKbM0g/6tFV6bjAd87rncw7bk+S9yOS/xfaR2GMPbGIcg0t5enpMpVlgF+0RgZq4dRq1DysITjX3PlTIDYjvFs5lJcFrrVcfqhSEoo3unWWdKr7wcj+JbR+4pp9GZJ7eLbNNj6JUBDiEVTWn187L+uUprA96DUGpPY1eW62C4LTV6kOHjqCo6IP12lthxnR5qz7IDQReNGWdGlinmnFQp8XU1POIvSTjYDrkdw8Q6AC36Ob8U9xw0lR1RDURoCELZiIMqxteW0ddLBZCs/bVHuNXN3QcQjoCaRQBXJrQW12UezSXu8LJCPxQsv/tieem9rIvAV1xFQSFinFDp1nVrlzrCqYiwIuvbEAVzJPU4hADfmAMR3niuRPYzwKfxWW48r+wj8dJNC2P+sE18dmMxME0uWGRT1ST3n7mhwFOoiHQun0ZmAwiUxix0I5sHvI8Kh0BvI3QFSCby1hgrFyfgYeH7dn2h2TIYnd0JpuBAGkjtEAPY9XHBU5VHybOYxsg0NazQQgMdpDuryUxEajW6up+XAWDq8SeTN7wliyExs1X9VHWg1NFRBvFF6/jxPFKWp0i0PqTsIHlU+Vhp4F6NZGM0E54qCQIxkuhrilk0DoY2JfQ9uPqj8S+Cqa3DRH2jnJiOL2yyShizEcRCoKsdVSd9uuq612NiiCbHYWpnd5m3kGThgAWQnTUUYBL1DoIvYcMHaH4mr1s13X3B/Z30GEgyIaooyx97Ujp965jgNDh7Eq6k32hd00mAuy8gstzJnGTKh1b7ztosRDahT1iYHtMXvKi7TPbXZuzKl8UnXif7YioKtDAPxNYSmLz7f52Zt/ALcXXvwKojB+vmjOZ+ZIf0c6mlhFo67TrlTQYu/aJSN9YybdKUBCaX72auizDdtjLs+lUBLnlx3j5HYZ9K52GoFLx6eo9uIz9gg9/Z2vQEZSBaDZCZN8vqsYbn01oIAgJ/e1/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Activity_Code_2 = _t, Code_2_Desc = _t, Project_Number = _t, #"142" = _t, #"event id" = _t, Index = _t, #"Max Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Max Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Closest Date", (k)=> Table.Sort(Table.AddColumn(Dates,"diff",each Number.Abs(Number.From([Date]-k[Max Date]))),{{"diff", Order.Ascending}}){0}[Date]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Exact Match", (k)=> try Table.SelectRows(Dates,each [Date]=k[Max Date]){0}[Date] otherwise null) in #"Added Custom1"

see attached.

topic Re: lookup between two dates (true function) in DAX Commands and Tips

lookup between two dates (true function)

Re: lookup between two dates (true function)

Re: lookup between two dates (true function)