Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
)
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
)
Perfect! You are the best!
@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
@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):
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
16 | |
13 |