topic Accumulate last value of products in DAX Commands and Tips

Accumulate last value of products

pg1980 — Tue, 17 May 2022 22:19:07 GMT

Hi,

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

Producto	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

Re: Accumulate last value of products

amitchandak — Wed, 18 May 2022 02:01:23 GMT

pg1980 , Try a measure like

lastnonblankvalue(Table[Date], sum(Table[Price]))

calculate(lastnonblankvalue(Table[Date], sum(Table[Price])), allexcept(Table, Table[Month Year]))

You need a month year column for above

Re: Accumulate last value of products

pg1980 — Wed, 18 May 2022 02:31:54 GMT

amitchandak, thanks for your answer but i had tried measure like you recomended and i have the same result.

For example in this case, is not summning the total (either accumulating the last value):

amount price = lastnonblankvalue(DatesTable[Date], sum(Sheet1[Price]))

Amountprice = calculate(lastnonblankvalue(DatesTable[Date], sum(Sheet1[Price])), allexcept(DatesTable, DatesTable[year-month]))

Do you think you could help me? Has it ever happened to you?

Re: Accumulate last value of products

tamerj1 — Wed, 18 May 2022 04:30:25 GMT

Hi pg1980
You use https://we.tl/t-mHmrvvIrdI

Last Price = VAR CurrentDate = MAX ( DatesTable[Date] ) RETURN CALCULATE ( SUMX ( SUMMARIZE ( Sheet1, Sheet1[ Category], Sheet1[Producto] ), CALCULATE ( VAR ProductTable = CALCULATETABLE ( Sheet1, ALLEXCEPT ( Sheet1, Sheet1[Producto], Sheet1[ Category] ), DatesTable[Date] <= CurrentDate ) VAR MaxDate = MAXX ( ProductTable, Sheet1[ Date] ) VAR LastDateTable = FILTER ( ProductTable, Sheet1[ Date] = MaxDate ) VAR LastPrice = MAXX ( LastDateTable, Sheet1[Price] ) RETURN LastPrice ) ), DatesTable[Date] <= CurrentDate )

Re: Accumulate last value of products

pg1980 — Wed, 18 May 2022 05:02:33 GMT

Perfect! You are the best!