cancel
Showing results for
Did you mean:
Frequent Visitor

## Total amount on Latest date

Friends,

i have product table, that contains product id,date,amount. i would like to get total amount on latest date latest value by index. For example on date 14-Nov, total amount will be (290+85) and when i select product id 4114 from slicer the total amount will be 290 only

2 ACCEPTED SOLUTIONS
Community Champion

try to create a measure with the code below:

SumAmount =
VAR Table1=
SUMMARIZE(
TableName,
TableName[product_id],
TableName[Date]
)
VAR Table2=
Table1,
"Index",
CALCULATE(MAX(TableName[Index]))
)
VAR MaxDate = MAX(TableName[Date])
VAR Table3 =
FILTER(
Table2,
TableName[Date] = MaxDate
)
VAR Table4 =
TREATAS(
Table3,
TableName[product_id],
TableName[Date],
TableName[Index]
)
VAR RESULT =
CALCULATE(
SUM(TableName[Amount]),
Table4
)
RETURN
RESULT

I tried and it worked as below:

4 REPLIES 4
Community Champion

why skip product 2116?

Frequent Visitor

by default the date will be latest date from entire table. when we select date slicer. if we select 15 Sep the amount will be 111( or it will add also another product it will fell on 15 sep)

Community Champion

try to create a measure with the code below:

SumAmount =
VAR Table1=
SUMMARIZE(
TableName,
TableName[product_id],
TableName[Date]
)
VAR Table2=
Table1,
"Index",
CALCULATE(MAX(TableName[Index]))
)
VAR MaxDate = MAX(TableName[Date])
VAR Table3 =
FILTER(
Table2,
TableName[Date] = MaxDate
)
VAR Table4 =
TREATAS(
Table3,
TableName[product_id],
TableName[Date],
TableName[Index]
)
VAR RESULT =
CALCULATE(
SUM(TableName[Amount]),
Table4
)
RETURN
RESULT

I tried and it worked as below:

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors