Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mussaenda
Super User
Super User

Last Value of Every Month

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.022319     6,564,571.02
30/08/2019 00:00:00                                              6,784,825.872318     6,784,825.87
29/08/2019 00:00:00                                              6,784,876.252317 
28/08/2019 00:00:00                                           10,646,261.632316 
27/08/2019 00:00:00                                           10,144,474.032315 
25/08/2019 00:00:00                                              9,680,986.072314 
24/08/2019 00:00:00                                              9,351,917.252313 
22/08/2019 00:00:00                                              8,903,386.662312 
21/08/2019 00:00:00                                              8,829,409.802311 
20/08/2019 00:00:00                                              8,504,821.452310 
17/08/2019 00:00:00                                              8,506,139.432309 
15/08/2019 00:00:00                                              8,487,484.412308 
14/08/2019 00:00:00                                              9,475,110.502307 
09/08/2019 00:00:00                                              8,967,067.262306 
08/08/2019 00:00:00                                              8,828,632.282305 
07/08/2019 00:00:00                                              8,584,191.722304 
06/08/2019 00:00:00                                              8,278,699.832303 
04/08/2019 00:00:00                                              7,456,815.722302 
03/08/2019 00:00:00                                              7,410,078.322301 
01/08/2019 00:00:00                                              7,325,497.012300 
31/07/2019 00:00:00                                              7,344,648.912299     7,344,648.91
30/07/2019 00:00:00                                           12,384,076.802298 
29/07/2019 00:00:00                                           12,250,113.292297 
28/07/2019 00:00:00                                           11,953,405.052296 
27/07/2019 00:00:00                                           11,922,883.412295 
26/07/2019 00:00:00                                           11,916,823.012294 
25/07/2019 00:00:00                                           11,798,432.562293 
24/07/2019 00:00:00                                           11,756,496.052292 
23/07/2019 00:00:00                                           11,602,221.322291 
22/07/2019 00:00:00                                           11,531,069.082290 
21/07/2019 00:00:00                                           11,373,897.132289 
20/07/2019 00:00:00                                           11,441,593.592288 
18/07/2019 00:00:00                                           10,621,671.322287 
17/07/2019 00:00:00                                           11,395,626.612286 
16/07/2019 00:00:00                                           11,254,372.452285 
15/07/2019 00:00:00                                           10,840,902.512284 
14/07/2019 00:00:00                                           11,445,551.682283 
13/07/2019 00:00:00                                           11,380,865.102282 
11/07/2019 00:00:00                                           11,321,219.692281 
10/07/2019 00:00:00                                           11,191,798.572280 
09/07/2019 00:00:00                                           11,150,184.152279 
08/07/2019 00:00:00                                           10,255,781.712278 
06/07/2019 00:00:00                                           11,796,536.572277 
05/07/2019 00:00:00                                           11,101,169.962276 
04/07/2019 00:00:00                                           11,039,629.962275 
03/07/2019 00:00:00                                           11,013,240.082274 
02/07/2019 00:00:00                                           10,915,945.952273 
01/07/2019 00:00:00                                           10,857,137.882272 
30/06/2019 00:00:00                                           11,102,376.962271   11,102,376.96
29/06/2019 00:00:00                                              9,879,163.112270 
28/06/2019 00:00:00                                              9,879,178.102269 
27/06/2019 00:00:00                                              9,822,649.142268 
26/06/2019 00:00:00                                           16,028,568.402267 
25/06/2019 00:00:00                                           15,411,345.262266 
24/06/2019 00:00:00                                           14,814,525.102265 
23/06/2019 00:00:00                                           15,374,982.222264 
22/06/2019 00:00:00                                           15,398,195.102263 
20/06/2019 00:00:00                                           14,987,452.752262 
19/06/2019 00:00:00                                           14,889,967.022261 
18/06/2019 00:00:00                                           14,713,552.742260 
17/06/2019 00:00:00                                           14,352,570.702259 
16/06/2019 00:00:00                                           15,421,314.712258 
15/06/2019 00:00:00                                           15,270,984.002257 
13/06/2019 00:00:00                                           15,335,104.282256 
12/06/2019 00:00:00                                           14,879,602.152255 
11/06/2019 00:00:00                                           14,180,981.892254 
10/06/2019 00:00:00                                           14,016,994.652253 
09/06/2019 00:00:00                                           13,881,245.452252 
08/06/2019 00:00:00                                           13,190,966.752251 
07/06/2019 00:00:00                                           12,986,507.782250 
06/06/2019 00:00:00                                           12,936,747.022249 
02/06/2019 00:00:00                                           12,477,657.822248 
01/06/2019 00:00:00                                           12,477,725.942247 
31/05/2019 00:00:00                                           12,443,463.452246   12,443,463.45
30/05/2019 00:00:00                                           12,249,631.132245 
29/05/2019 00:00:00                                           16,926,496.962244 
28/05/2019 00:00:00                                           16,474,498.442243 
27/05/2019 00:00:00                                           16,405,291.782242 
26/05/2019 00:00:00                                           16,405,305.922241 
25/05/2019 00:00:00                                           16,236,689.472240 
24/05/2019 00:00:00                                           16,220,981.512239 
23/05/2019 00:00:00                                           15,942,284.552238 
22/05/2019 00:00:00                                           15,682,658.162237 
21/05/2019 00:00:00                                           15,542,894.552236 
20/05/2019 00:00:00                                           17,283,482.012235 
19/05/2019 00:00:00                                           17,191,384.512234 
18/05/2019 00:00:00                                           17,106,850.272233 
16/05/2019 00:00:00                                           17,002,308.082232 
15/05/2019 00:00:00                                           16,771,051.572231 
14/05/2019 00:00:00                                           16,948,269.262230 
13/05/2019 00:00:00                                           16,299,412.402229 
12/05/2019 00:00:00                                           16,252,739.762228 
11/05/2019 00:00:00                                           15,368,532.152227 
10/05/2019 00:00:00                                           15,368,577.072226 
09/05/2019 00:00:00                                           14,922,381.872225 
08/05/2019 00:00:00                                           14,858,479.232224 
07/05/2019 00:00:00                                           14,333,399.342223 
06/05/2019 00:00:00                                           13,706,829.492222 
05/05/2019 00:00:00                                           13,236,172.792221 
02/05/2019 00:00:00                                           13,073,516.202220 
01/05/2019 00:00:00                                           13,038,987.112219 
30/04/2019 00:00:00                                           12,966,690.092218   12,966,690.09
29/04/2019 00:00:00                                           17,070,367.662217 
28/04/2019 00:00:00                                           17,059,598.802216 
27/04/2019 00:00:00                                           17,064,113.162215 
26/04/2019 00:00:00                                           16,808,617.542214 
25/04/2019 00:00:00                                           16,808,672.042213 
24/04/2019 00:00:00                                           16,780,668.212212 
23/04/2019 00:00:00                                           16,742,610.732211 
22/04/2019 00:00:00                                           16,382,162.332210 
21/04/2019 00:00:00                                           15,864,695.342209 
20/04/2019 00:00:00                                           15,904,837.572208 
18/04/2019 00:00:00                                           15,865,711.012207 
17/04/2019 00:00:00                                           17,386,357.162206 
16/04/2019 00:00:00                                           17,218,986.272205 
15/04/2019 00:00:00                                           17,219,000.412204 
14/04/2019 00:00:00                                           17,646,418.812203 
13/04/2019 00:00:00                                           17,333,572.422202 
11/04/2019 00:00:00                                           17,100,053.952201 
10/04/2019 00:00:00                                           17,080,604.752200 
09/04/2019 00:00:00                                           17,210,955.812199 
08/04/2019 00:00:00                                           16,543,353.822198 
07/04/2019 00:00:00                                           16,417,625.022197 
06/04/2019 00:00:00                                           14,970,108.892196 
05/04/2019 00:00:00                                           14,970,123.862195 
04/04/2019 00:00:00                                           14,882,377.112194 
03/04/2019 00:00:00                                           14,679,561.972193 
02/04/2019 00:00:00                                           14,489,547.642192 
01/04/2019 00:00:00                                           14,926,570.782191 
31/03/2019 00:00:00                                           14,340,613.322190   14,340,613.32

 

 

2 ACCEPTED SOLUTIONS

Hi @mussaenda 

 

I am attaching pbix file with your sample data and formula

It works with sample data

 

 

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

@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"

Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

hi @Zubair_Muhammad ,

 

it is giving me blank

Hi @mussaenda 

 

I am attaching pbix file with your sample data and formula

It works with sample data

 

 

 

 


Regards
Zubair

Please try my custom visuals

My fault, i misused one column.

Worked. Thank you!

 

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

@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"

Regards
Zubair

Please try my custom visuals

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

Thanks @mussaenda  for the kind words Smiley Happy


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors