Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Good Day,
I have a data like this and those in red font color are what i need.
The Value needed is the value of max date per month.
Amount is cumulative that's why i only wanted to get the last value per month.
I tried it in power query but i did not succeed.
In DAX, i did not succeed.
Also, It will be nice if we will use calculated column for future steps.
Thank you
Date | AMT | Index | Value Needed |
01/09/2019 00:00:00 | 6,564,571.02 | 2319 | 6,564,571.02 |
30/08/2019 00:00:00 | 6,784,825.87 | 2318 | 6,784,825.87 |
29/08/2019 00:00:00 | 6,784,876.25 | 2317 | |
28/08/2019 00:00:00 | 10,646,261.63 | 2316 | |
27/08/2019 00:00:00 | 10,144,474.03 | 2315 | |
25/08/2019 00:00:00 | 9,680,986.07 | 2314 | |
24/08/2019 00:00:00 | 9,351,917.25 | 2313 | |
22/08/2019 00:00:00 | 8,903,386.66 | 2312 | |
21/08/2019 00:00:00 | 8,829,409.80 | 2311 | |
20/08/2019 00:00:00 | 8,504,821.45 | 2310 | |
17/08/2019 00:00:00 | 8,506,139.43 | 2309 | |
15/08/2019 00:00:00 | 8,487,484.41 | 2308 | |
14/08/2019 00:00:00 | 9,475,110.50 | 2307 | |
09/08/2019 00:00:00 | 8,967,067.26 | 2306 | |
08/08/2019 00:00:00 | 8,828,632.28 | 2305 | |
07/08/2019 00:00:00 | 8,584,191.72 | 2304 | |
06/08/2019 00:00:00 | 8,278,699.83 | 2303 | |
04/08/2019 00:00:00 | 7,456,815.72 | 2302 | |
03/08/2019 00:00:00 | 7,410,078.32 | 2301 | |
01/08/2019 00:00:00 | 7,325,497.01 | 2300 | |
31/07/2019 00:00:00 | 7,344,648.91 | 2299 | 7,344,648.91 |
30/07/2019 00:00:00 | 12,384,076.80 | 2298 | |
29/07/2019 00:00:00 | 12,250,113.29 | 2297 | |
28/07/2019 00:00:00 | 11,953,405.05 | 2296 | |
27/07/2019 00:00:00 | 11,922,883.41 | 2295 | |
26/07/2019 00:00:00 | 11,916,823.01 | 2294 | |
25/07/2019 00:00:00 | 11,798,432.56 | 2293 | |
24/07/2019 00:00:00 | 11,756,496.05 | 2292 | |
23/07/2019 00:00:00 | 11,602,221.32 | 2291 | |
22/07/2019 00:00:00 | 11,531,069.08 | 2290 | |
21/07/2019 00:00:00 | 11,373,897.13 | 2289 | |
20/07/2019 00:00:00 | 11,441,593.59 | 2288 | |
18/07/2019 00:00:00 | 10,621,671.32 | 2287 | |
17/07/2019 00:00:00 | 11,395,626.61 | 2286 | |
16/07/2019 00:00:00 | 11,254,372.45 | 2285 | |
15/07/2019 00:00:00 | 10,840,902.51 | 2284 | |
14/07/2019 00:00:00 | 11,445,551.68 | 2283 | |
13/07/2019 00:00:00 | 11,380,865.10 | 2282 | |
11/07/2019 00:00:00 | 11,321,219.69 | 2281 | |
10/07/2019 00:00:00 | 11,191,798.57 | 2280 | |
09/07/2019 00:00:00 | 11,150,184.15 | 2279 | |
08/07/2019 00:00:00 | 10,255,781.71 | 2278 | |
06/07/2019 00:00:00 | 11,796,536.57 | 2277 | |
05/07/2019 00:00:00 | 11,101,169.96 | 2276 | |
04/07/2019 00:00:00 | 11,039,629.96 | 2275 | |
03/07/2019 00:00:00 | 11,013,240.08 | 2274 | |
02/07/2019 00:00:00 | 10,915,945.95 | 2273 | |
01/07/2019 00:00:00 | 10,857,137.88 | 2272 | |
30/06/2019 00:00:00 | 11,102,376.96 | 2271 | 11,102,376.96 |
29/06/2019 00:00:00 | 9,879,163.11 | 2270 | |
28/06/2019 00:00:00 | 9,879,178.10 | 2269 | |
27/06/2019 00:00:00 | 9,822,649.14 | 2268 | |
26/06/2019 00:00:00 | 16,028,568.40 | 2267 | |
25/06/2019 00:00:00 | 15,411,345.26 | 2266 | |
24/06/2019 00:00:00 | 14,814,525.10 | 2265 | |
23/06/2019 00:00:00 | 15,374,982.22 | 2264 | |
22/06/2019 00:00:00 | 15,398,195.10 | 2263 | |
20/06/2019 00:00:00 | 14,987,452.75 | 2262 | |
19/06/2019 00:00:00 | 14,889,967.02 | 2261 | |
18/06/2019 00:00:00 | 14,713,552.74 | 2260 | |
17/06/2019 00:00:00 | 14,352,570.70 | 2259 | |
16/06/2019 00:00:00 | 15,421,314.71 | 2258 | |
15/06/2019 00:00:00 | 15,270,984.00 | 2257 | |
13/06/2019 00:00:00 | 15,335,104.28 | 2256 | |
12/06/2019 00:00:00 | 14,879,602.15 | 2255 | |
11/06/2019 00:00:00 | 14,180,981.89 | 2254 | |
10/06/2019 00:00:00 | 14,016,994.65 | 2253 | |
09/06/2019 00:00:00 | 13,881,245.45 | 2252 | |
08/06/2019 00:00:00 | 13,190,966.75 | 2251 | |
07/06/2019 00:00:00 | 12,986,507.78 | 2250 | |
06/06/2019 00:00:00 | 12,936,747.02 | 2249 | |
02/06/2019 00:00:00 | 12,477,657.82 | 2248 | |
01/06/2019 00:00:00 | 12,477,725.94 | 2247 | |
31/05/2019 00:00:00 | 12,443,463.45 | 2246 | 12,443,463.45 |
30/05/2019 00:00:00 | 12,249,631.13 | 2245 | |
29/05/2019 00:00:00 | 16,926,496.96 | 2244 | |
28/05/2019 00:00:00 | 16,474,498.44 | 2243 | |
27/05/2019 00:00:00 | 16,405,291.78 | 2242 | |
26/05/2019 00:00:00 | 16,405,305.92 | 2241 | |
25/05/2019 00:00:00 | 16,236,689.47 | 2240 | |
24/05/2019 00:00:00 | 16,220,981.51 | 2239 | |
23/05/2019 00:00:00 | 15,942,284.55 | 2238 | |
22/05/2019 00:00:00 | 15,682,658.16 | 2237 | |
21/05/2019 00:00:00 | 15,542,894.55 | 2236 | |
20/05/2019 00:00:00 | 17,283,482.01 | 2235 | |
19/05/2019 00:00:00 | 17,191,384.51 | 2234 | |
18/05/2019 00:00:00 | 17,106,850.27 | 2233 | |
16/05/2019 00:00:00 | 17,002,308.08 | 2232 | |
15/05/2019 00:00:00 | 16,771,051.57 | 2231 | |
14/05/2019 00:00:00 | 16,948,269.26 | 2230 | |
13/05/2019 00:00:00 | 16,299,412.40 | 2229 | |
12/05/2019 00:00:00 | 16,252,739.76 | 2228 | |
11/05/2019 00:00:00 | 15,368,532.15 | 2227 | |
10/05/2019 00:00:00 | 15,368,577.07 | 2226 | |
09/05/2019 00:00:00 | 14,922,381.87 | 2225 | |
08/05/2019 00:00:00 | 14,858,479.23 | 2224 | |
07/05/2019 00:00:00 | 14,333,399.34 | 2223 | |
06/05/2019 00:00:00 | 13,706,829.49 | 2222 | |
05/05/2019 00:00:00 | 13,236,172.79 | 2221 | |
02/05/2019 00:00:00 | 13,073,516.20 | 2220 | |
01/05/2019 00:00:00 | 13,038,987.11 | 2219 | |
30/04/2019 00:00:00 | 12,966,690.09 | 2218 | 12,966,690.09 |
29/04/2019 00:00:00 | 17,070,367.66 | 2217 | |
28/04/2019 00:00:00 | 17,059,598.80 | 2216 | |
27/04/2019 00:00:00 | 17,064,113.16 | 2215 | |
26/04/2019 00:00:00 | 16,808,617.54 | 2214 | |
25/04/2019 00:00:00 | 16,808,672.04 | 2213 | |
24/04/2019 00:00:00 | 16,780,668.21 | 2212 | |
23/04/2019 00:00:00 | 16,742,610.73 | 2211 | |
22/04/2019 00:00:00 | 16,382,162.33 | 2210 | |
21/04/2019 00:00:00 | 15,864,695.34 | 2209 | |
20/04/2019 00:00:00 | 15,904,837.57 | 2208 | |
18/04/2019 00:00:00 | 15,865,711.01 | 2207 | |
17/04/2019 00:00:00 | 17,386,357.16 | 2206 | |
16/04/2019 00:00:00 | 17,218,986.27 | 2205 | |
15/04/2019 00:00:00 | 17,219,000.41 | 2204 | |
14/04/2019 00:00:00 | 17,646,418.81 | 2203 | |
13/04/2019 00:00:00 | 17,333,572.42 | 2202 | |
11/04/2019 00:00:00 | 17,100,053.95 | 2201 | |
10/04/2019 00:00:00 | 17,080,604.75 | 2200 | |
09/04/2019 00:00:00 | 17,210,955.81 | 2199 | |
08/04/2019 00:00:00 | 16,543,353.82 | 2198 | |
07/04/2019 00:00:00 | 16,417,625.02 | 2197 | |
06/04/2019 00:00:00 | 14,970,108.89 | 2196 | |
05/04/2019 00:00:00 | 14,970,123.86 | 2195 | |
04/04/2019 00:00:00 | 14,882,377.11 | 2194 | |
03/04/2019 00:00:00 | 14,679,561.97 | 2193 | |
02/04/2019 00:00:00 | 14,489,547.64 | 2192 | |
01/04/2019 00:00:00 | 14,926,570.78 | 2191 | |
31/03/2019 00:00:00 | 14,340,613.32 | 2190 | 14,340,613.32 |
Solved! Go to Solution.
Hi @mussaenda
I am attaching pbix file with your sample data and formula
It works with sample data
Here is a way of doing it in Power Query
Please see attached file's Query Editor for steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVpLjuU4DLtKodeGWz//5iqNvv81hn52npXZDeRC1SooxlBIiZL8588v4t80fgvx+CH65/P7K/36+V8/NZVqqTTOJPhnUR4PxuvR3/Tnl9Jv6uH3tW6pS8m9rff18z73aL5PxrX3tZqlrPfN1/4s/B7EZ0rVapLKuepCrwe9xdHZLFmzTBu9HPQSj81ItVMavWba38IOvt3A18JpcPvGXg++xPF7GqRJcf5aF74cfL6B32Uko5E7LXw++Be00FOhSXjOtuNDX3yOsmfj18Q6si3+0Dj4F/jTk/WGP8vGC78f/Cv8sVYSM+Wy4k9Hu3QhN4A/tSWq4OfiDx31UjQ3/Cz+9FRVsvSFf/RLd74vchsPzm3lbjr6pXoDXxrOP8D/zZ+jX7rwfcGdUlPn8j3/0S/pFXzkUGo968Y/+qUL+aEllZJstEyb/0e/CvwWx0f+r9bz+ODL+Nbm16OnNofex4JMaohW3dlOxlHzrMRRdCmoZ6xZxkJ/1+EYOqpMUWTqkj8SA/q7DofRRVLvuvOcDFeH6wV0hgpEN4tkuCpc4uhtIE8jB5W60F0NtgvoULCN+o27q8AaR68E3qA+Lv3KcPVX4uhFGdl/5E/ZAvpRr0TVC3Rtmjpyw8f0iPRTeyWsVU5mOP/QXJaauqu8YTXB1Qpi375x70erfEFNOgreANe2+N6PVvmCmqQYYi/bU0k/WuWwmih1g2MmqGmf/WiVL6jJrKRS0E0sRvajVb6gJoXb77VkXvm9H63yDb6DM8Ij183Io1W+wHe4nJnJcmkL/WiVwrUJ6LM2wcfy4kw7WqULapJS0IXCpS3OtKNVusD3NtCoa30i05xHvlA9mPCHHDlW9WjOIV/gOyl6UDnozh9f4DuxJjF68ntz7jhcPZAFuKRhJY/NGeeNw2qCTktD59Zy32c/Wp1Or4a/KtwenN4Td14I7yeP8Qu97Ge2cb0NkEgzbwG4Qtuv4cPm78RWXaltV/Bh/6qN/JmPAN8Z4xr9GDURmsRSe7Z9emeMSxQd7clMzaDpanGlOmNsUXRD82apyLekVGeMNX52bZZGR/u8bEh1xlguoMMY8zhnd8Y4LLF57tneSm4rPVRXbKOamnHvYw4v9tBWqiu2UUUBvSFxlnn2zff3WCqKrkVSaZTbintxQ6mwmsB32BBle4ptccb4gpqQu/BlLdM+uzPGN/iuBQnY9rhIijPGYb7bJ0uioXpMTnHGmOPo/Bklc+7L/hVnjC+oiZAnx7Bc99ldsQ2rCW1ah3VFjtwtQ3FjqLCaFDkGkan1yQTFDaHCapI5vk+FWm6bM84Yh9UEdK2p2TfPmDPGYUZKstZSLTA5G90Z4zAjF3pDbRori9nR6hzOlSi6aTJ4ms0Zq9tCvZ48fi36MgQ+VeVnlmCuzI4oOoQla4az3aC5Mtvj6IYibujcbH8GV2bbBXRCUp4T8EV/c/OnegddCRTaBHXzpxJHF4ir9pFtdW7mbLFdQJeVkPekQp0p1ij67HzAShTCsuivzhJLHL12GO4CQ78Yqc4Sh6VbUsHZ+zhnd5Y4rNWGqED+MK17mquuzIa12j5zEJ1x3+iuzIa1CnSq6Dwpy2KkuvlTWE0t0ewtqT/9uDpLfEFNraHjL/zMQdRZ4gtqGtYTGsunjVJnicNqglbHQKMmTwsozhKH1QR0GO6mI7d1dnGW+IKatKJ91a+tFGeJw2ra6K3tGwoibv4UVpN99jo6TetGd/OnsJpguEtH9QNnVtUWN38KVz60UapoYUfWVVfFWeKwVjW1mQkEtWnZeXGWOKxV/VQ+bmgwN7qzxGG+a6KG9pVrlq0mZ4nDfAe69tncP8MsPlqdTs+ihrvCEwzKtCLz3I56P3mMX+xlSMjoZrW2fX1G/nM1KoxeRiowfnud/J+rUWH0ap918rYI/F7JxtBBfeqpcstliYvfK9kr6KA/bfT3SjaK3tCJ19qzbIK+V7JhdJinypTbSmr8XslG0RXGj6tk3ejvlWwMvaQOztRRnpRJ75VsFH3My1zaHgNC75Vs/OwlNebHVtJ7JRtVk3bEHp34VhO9V7JRdKSxzzXDbSvpvZKNow9YS3ouZ9B7JRtFnxc8jZHFNvp7JRuOO4p4mcvq1WDSeyUbRWciZGF9VlT0XsmGM/DMM2TPXMtdfqILtUnmiq2UHXce75VsNM8Ug3lCZNbkid1VJwqrafIFvJGyp2bsrjpRWE0wrajajDZqTVrZXXWisJo2uiAydaE7SxxW09xbzPXiY57YXXWisJqQ2xscR+X8CTjQnSUO1yZDUw90g1uyhe4scVir9hnCfbYifaEfrc5ppUabBYNW4ZZ0M3IjvJ/8/fsv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #" AMT " = _t, Index = _t, #" Value Needed" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {" AMT ", type number}, {"Index", Int64.Type}, {" Value Needed", type number}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type datetime}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{" AMT ", "AMT"}}), #"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type), #"Grouped Rows" = Table.Group(#"Inserted Month", {"Year", "Month"}, {{"ALL", each _, type table [Date=datetime, #" AMT "=number, Index=number, #" Value Needed"=number, Year=number, Month=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let mydate=List.Max([ALL][Date]) in Table.AddColumn([ALL], "Column",each (if [Date]=mydate then [AMT] else null))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Year", "Month", "ALL"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "AMT", "Index", " Value Needed", "Column"}, {"Date", "AMT", "Index", " Value Needed", "Column"}) in #"Expanded Custom"
Try this column
Column = VAR maxdate = CALCULATE ( MAX ( TableName[Date] ), ALLEXCEPT ( TableName, TableName[Date].[Month], TableName[Date].[Year] ) ) RETURN IF ( [Date] = maxdate, CALCULATE ( SUM ( TableName[ AMT ] ), TableName[Date] = maxdate ) )
Hi @mussaenda
I am attaching pbix file with your sample data and formula
It works with sample data
My fault, i misused one column.
Worked. Thank you!
Also wondering if this can also be done in power query.
Here is a way of doing it in Power Query
Please see attached file's Query Editor for steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVpLjuU4DLtKodeGWz//5iqNvv81hn52npXZDeRC1SooxlBIiZL8588v4t80fgvx+CH65/P7K/36+V8/NZVqqTTOJPhnUR4PxuvR3/Tnl9Jv6uH3tW6pS8m9rff18z73aL5PxrX3tZqlrPfN1/4s/B7EZ0rVapLKuepCrwe9xdHZLFmzTBu9HPQSj81ItVMavWba38IOvt3A18JpcPvGXg++xPF7GqRJcf5aF74cfL6B32Uko5E7LXw++Be00FOhSXjOtuNDX3yOsmfj18Q6si3+0Dj4F/jTk/WGP8vGC78f/Cv8sVYSM+Wy4k9Hu3QhN4A/tSWq4OfiDx31UjQ3/Cz+9FRVsvSFf/RLd74vchsPzm3lbjr6pXoDXxrOP8D/zZ+jX7rwfcGdUlPn8j3/0S/pFXzkUGo968Y/+qUL+aEllZJstEyb/0e/CvwWx0f+r9bz+ODL+Nbm16OnNofex4JMaohW3dlOxlHzrMRRdCmoZ6xZxkJ/1+EYOqpMUWTqkj8SA/q7DofRRVLvuvOcDFeH6wV0hgpEN4tkuCpc4uhtIE8jB5W60F0NtgvoULCN+o27q8AaR68E3qA+Lv3KcPVX4uhFGdl/5E/ZAvpRr0TVC3Rtmjpyw8f0iPRTeyWsVU5mOP/QXJaauqu8YTXB1Qpi375x70erfEFNOgreANe2+N6PVvmCmqQYYi/bU0k/WuWwmih1g2MmqGmf/WiVL6jJrKRS0E0sRvajVb6gJoXb77VkXvm9H63yDb6DM8Ij183Io1W+wHe4nJnJcmkL/WiVwrUJ6LM2wcfy4kw7WqULapJS0IXCpS3OtKNVusD3NtCoa30i05xHvlA9mPCHHDlW9WjOIV/gOyl6UDnozh9f4DuxJjF68ntz7jhcPZAFuKRhJY/NGeeNw2qCTktD59Zy32c/Wp1Or4a/KtwenN4Td14I7yeP8Qu97Ge2cb0NkEgzbwG4Qtuv4cPm78RWXaltV/Bh/6qN/JmPAN8Z4xr9GDURmsRSe7Z9emeMSxQd7clMzaDpanGlOmNsUXRD82apyLekVGeMNX52bZZGR/u8bEh1xlguoMMY8zhnd8Y4LLF57tneSm4rPVRXbKOamnHvYw4v9tBWqiu2UUUBvSFxlnn2zff3WCqKrkVSaZTbintxQ6mwmsB32BBle4ptccb4gpqQu/BlLdM+uzPGN/iuBQnY9rhIijPGYb7bJ0uioXpMTnHGmOPo/Bklc+7L/hVnjC+oiZAnx7Bc99ldsQ2rCW1ah3VFjtwtQ3FjqLCaFDkGkan1yQTFDaHCapI5vk+FWm6bM84Yh9UEdK2p2TfPmDPGYUZKstZSLTA5G90Z4zAjF3pDbRori9nR6hzOlSi6aTJ4ms0Zq9tCvZ48fi36MgQ+VeVnlmCuzI4oOoQla4az3aC5Mtvj6IYibujcbH8GV2bbBXRCUp4T8EV/c/OnegddCRTaBHXzpxJHF4ir9pFtdW7mbLFdQJeVkPekQp0p1ij67HzAShTCsuivzhJLHL12GO4CQ78Yqc4Sh6VbUsHZ+zhnd5Y4rNWGqED+MK17mquuzIa12j5zEJ1x3+iuzIa1CnSq6Dwpy2KkuvlTWE0t0ewtqT/9uDpLfEFNraHjL/zMQdRZ4gtqGtYTGsunjVJnicNqglbHQKMmTwsozhKH1QR0GO6mI7d1dnGW+IKatKJ91a+tFGeJw2ra6K3tGwoibv4UVpN99jo6TetGd/OnsJpguEtH9QNnVtUWN38KVz60UapoYUfWVVfFWeKwVjW1mQkEtWnZeXGWOKxV/VQ+bmgwN7qzxGG+a6KG9pVrlq0mZ4nDfAe69tncP8MsPlqdTs+ihrvCEwzKtCLz3I56P3mMX+xlSMjoZrW2fX1G/nM1KoxeRiowfnud/J+rUWH0ap918rYI/F7JxtBBfeqpcstliYvfK9kr6KA/bfT3SjaK3tCJ19qzbIK+V7JhdJinypTbSmr8XslG0RXGj6tk3ejvlWwMvaQOztRRnpRJ75VsFH3My1zaHgNC75Vs/OwlNebHVtJ7JRtVk3bEHp34VhO9V7JRdKSxzzXDbSvpvZKNow9YS3ouZ9B7JRtFnxc8jZHFNvp7JRuOO4p4mcvq1WDSeyUbRWciZGF9VlT0XsmGM/DMM2TPXMtdfqILtUnmiq2UHXce75VsNM8Ug3lCZNbkid1VJwqrafIFvJGyp2bsrjpRWE0wrajajDZqTVrZXXWisJo2uiAydaE7SxxW09xbzPXiY57YXXWisJqQ2xscR+X8CTjQnSUO1yZDUw90g1uyhe4scVir9hnCfbYifaEfrc5ppUabBYNW4ZZ0M3IjvJ/8/fsv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #" AMT " = _t, Index = _t, #" Value Needed" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {" AMT ", type number}, {"Index", Int64.Type}, {" Value Needed", type number}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type datetime}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{" AMT ", "AMT"}}), #"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type), #"Grouped Rows" = Table.Group(#"Inserted Month", {"Year", "Month"}, {{"ALL", each _, type table [Date=datetime, #" AMT "=number, Index=number, #" Value Needed"=number, Year=number, Month=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let mydate=List.Max([ALL][Date]) in Table.AddColumn([ALL], "Column",each (if [Date]=mydate then [AMT] else null))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Year", "Month", "ALL"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "AMT", "Index", " Value Needed", "Column"}, {"Date", "AMT", "Index", " Value Needed", "Column"}) in #"Expanded Custom"
What can I do without your help @Zubair_Muhammad !
Thank you for this! If you will held a training, I will voluntarily participate!
Really, Thank you!!