'Accumulate last value of products
Through measures I need to be able to accumulate the last value of the column “Price” by product into a table visualization. It should be able to show both, the value of the products and the total sum of the prices. This visualization has to be filtered by the column “year-month” of the Dates table. If the value is not found according to the “year-month” selected, it should take the last value of the column Price.
DatesTable = ADDCOLUMNS ( CALENDAR (FIRSTDATE(Sheet1[DATE]), TODAY()), "year", YEAR ( [Date] ), "MonthNumber", FORMAT ( [Date], "MM" ), "year-month", FORMAT ( [Date], "YYYY-MM" ), "month-year", FORMAT ( [Date], "MM-'YY" ) )
Table:
Product Date Category Price
A 30/01/2020 Category1 19682
B 30/01/2020 Category2 5648
C 07/02/2020 Category3 18096
B 15/03/2020 Category2 0
A 08/05/2020 Category1 0
B 30/07/2020 Category2 8042
B 31/07/2020 Category2 0
D 30/07/2020 Category1 54957
C 07/08/2020 Category3 23171
A 10/09/2020 Category1 1761,4
A 01/11/2020 Category1 0
A 08/11/2020 Category1 17841
D 30/01/2021 Category1 67201
C 07/02/2021 Category3 29256
C 16/04/2021 Category3 13619,3
A 08/05/2021 Category1 21690
A 11/06/2021 Category1 21377
D 30/07/2021 Category1 81255
C 07/08/2021 Category3 35811
D 03/11/2021 Category1 177231
D 03/11/2021 Category1 0
A 08/11/2021 Category1 27710
A 11/12/2021 Category1 26905
C 07/02/2022 Category3 48711
C 15/04/2022 Category3 0
D 02/05/2022 Category1 0
For example:
If I choose the column “Year-month” (DatesTable) and select: "2021-07". It must take the value of July or the last value according to the product. In this case for:
product A it is: 21377
product B it is: 0
product C it is: 13619.3
Product D it is: 81255
Category Price Category1 102632 Category2 0 Category3 13619,3 total: 116251,3
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|