Forum Discussion
Get last element/row for all weeks
Hello guys,
I am trying to get the Sales for my products for only the last day within the week. Do you have an idea, how I can solve this? I was thinking about creating a temporary table with ADDCOLUMNS or SUMMARIZECOLUMNS and iterating with SUMX over that table. But how can I say: "During the iteration take for every week the last day"?
Current Table:
| Date | Week | Product | Units Sold |
| 04.07.2022 | W28 | Amarilla | 8530 |
| 04.07.2022 | W28 | Paseo | 9123 |
| 04.07.2022 | W28 | Velo | 9003 |
| 05.07.2022 | W28 | Amarilla | 8686 |
| 05.07.2022 | W28 | Paseo | 9773 |
| 05.07.2022 | W28 | Velo | 8109 |
| 06.07.2022 | W28 | Amarilla | 9738 |
| 06.07.2022 | W28 | Paseo | 7095 |
| 06.07.2022 | W28 | Velo | 8144 |
| 07.07.2022 | W28 | Amarilla | 8684 |
| 07.07.2022 | W28 | Paseo | 8393 |
| 07.07.2022 | W28 | Velo | 7086 |
| 08.07.2022 | W28 | Amarilla | 8558 |
| 08.07.2022 | W28 | Paseo | 9151 |
| 08.07.2022 | W28 | Velo | 8170 |
| 09.07.2022 | W28 | Amarilla | 8643 |
| 09.07.2022 | W28 | Paseo | 7626 |
| 09.07.2022 | W28 | Velo | 8016 |
| 10.07.2022 | W28 | Amarilla | 9626 |
| 10.07.2022 | W28 | Paseo | 8441 |
| 10.07.2022 | W28 | Velo | 7130 |
| 11.07.2022 | W29 | Amarilla | 7403 |
| 11.07.2022 | W29 | Paseo | 9639 |
| 11.07.2022 | W29 | Velo | 7603 |
| 12.07.2022 | W29 | Amarilla | 8036 |
| 12.07.2022 | W29 | Paseo | 9340 |
| 12.07.2022 | W29 | Velo | 9854 |
| 13.07.2022 | W29 | Amarilla | 8232 |
| 13.07.2022 | W29 | Paseo | 7390 |
| 13.07.2022 | W29 | Velo | 9960 |
| 14.07.2022 | W29 | Amarilla | 8705 |
| 14.07.2022 | W29 | Paseo | 7284 |
| 14.07.2022 | W29 | Velo | 8250 |
| 15.07.2022 | W29 | Amarilla | 9121 |
| 15.07.2022 | W29 | Paseo | 8066 |
| 15.07.2022 | W29 | Velo | 8027 |
| 16.07.2022 | W29 | Amarilla | 7011 |
| 16.07.2022 | W29 | Paseo | 8643 |
| 16.07.2022 | W29 | Velo | 9487 |
| 17.07.2022 | W29 | Amarilla | 9250 |
| 17.07.2022 | W29 | Paseo | 9456 |
| 17.07.2022 | W29 | Velo | 8292 |
| 18.07.2022 | W30 | Amarilla | 7569 |
| 18.07.2022 | W30 | Paseo | 7521 |
| 18.07.2022 | W30 | Velo | 7019 |
| 19.07.2022 | W30 | Amarilla | 8477 |
| 19.07.2022 | W30 | Paseo | 7804 |
| 19.07.2022 | W30 | Velo | 9804 |
| 20.07.2022 | W30 | Amarilla | 8139 |
| 20.07.2022 | W30 | Paseo | 9269 |
| 20.07.2022 | W30 | Velo | 7179 |
| 21.07.2022 | W30 | Amarilla | 8107 |
| 21.07.2022 | W30 | Paseo | 7289 |
| 21.07.2022 | W30 | Velo | 8184 |
| 22.07.2022 | W30 | Amarilla | 7181 |
| 22.07.2022 | W30 | Paseo | 9233 |
| 22.07.2022 | W30 | Velo | 8753 |
| 23.07.2022 | W30 | Amarilla | 8651 |
| 23.07.2022 | W30 | Paseo | 7206 |
| 23.07.2022 | W30 | Velo | 8362 |
| 24.07.2022 | W30 | Amarilla | 9807 |
| 24.07.2022 | W30 | Paseo | 7413 |
| 24.07.2022 | W30 | Velo | 7407 |
Expected:
| Date | Week | Product | Units Sold |
| 10.07.2022 | W28 | Amarilla | 9626 |
| 10.07.2022 | W28 | Paseo | 8441 |
| 10.07.2022 | W28 | Velo | 7130 |
| 17.07.2022 | W29 | Amarilla | 9250 |
| 17.07.2022 | W29 | Paseo | 9456 |
| 17.07.2022 | W29 | Velo | 8292 |
| 24.07.2022 | W30 | Amarilla | 9807 |
| 24.07.2022 | W30 | Paseo | 7413 |
| 24.07.2022 | W30 | Velo | 7407 |
Thanks for all help!
Anonymous , refer my blog on this
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0
Or try like
calculate(lastnonblankvalues(Table[Date], Sum(Table[Unit]) ) )
or
calculate(lastnonblankvalues(Table[Date], Sum(Table[Unit]) ), filter(allselected(Table), Table[Week] = max(Table[Week]) && Table[Product] = Max(Table[Product]) ) )
2 Replies
- amitchandak
Super User
Anonymous , refer my blog on this
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0
Or try like
calculate(lastnonblankvalues(Table[Date], Sum(Table[Unit]) ) )
or
calculate(lastnonblankvalues(Table[Date], Sum(Table[Unit]) ), filter(allselected(Table), Table[Week] = max(Table[Week]) && Table[Product] = Max(Table[Product]) ) )