Accumulate last value of products

[pg1980]

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

[tamerj1 · Accepted solution]

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
    )

[pg1980]

Perfect! You are the best!

[amitchandak]

@pg1980 , Try a measure like

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

or

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

You need a month year column for above

[pg1980]

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