Last Value of Every Month

[mussaenda]

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

[Zubair_Muhammad · Accepted solution]

Hi @mussaenda 

I am attaching pbix file with your sample data and formula

It works with sample data

[Zubair_Muhammad]

@mussaenda 

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 )
    )

[mussaenda]

hi @Zubair_Muhammad ,

it is giving me blank

[Zubair_Muhammad · Accepted solution]

Hi @mussaenda 

I am attaching pbix file with your sample data and formula

It works with sample data

[mussaenda]

My fault, i misused one column.

Worked. Thank you!

Also wondering if this can also be done in power query.

[Zubair_Muhammad · Accepted solution]

@mussaenda 

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"

[mussaenda]

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!!

[Zubair_Muhammad]

Thanks @mussaenda  for the kind words