Forum Discussion

mihzuk's avatar
mihzuk
Frequent Visitor
2 years ago

Value on last date

Hi,    I need a measure that calculates the sum of quantities on the latest available date. This is my example table: Item1 Location1 QTY1 Date1 A X 1 2.2.2023 C X 1 2.2.2023 ...
Need Help
v-weiyan1-msft's avatar
v-weiyan1-msft
Community Support
2 years ago

Hi mihzuk ,

 

Based on the sample and description you provided, You may also consider try the following steps:

1. Please try code as below to Create Calculated column.

Index = RANKX('Table','Table'[Date1],,DESC,Dense)
MonthNumber = MONTH('Table'[Date1])

2. Use the following code to create measures.

Latest QTY =
VAR _Item =
    CALCULATE ( MAX ( 'Table'[Item1] ), FILTER ( 'Table', 'Table'[Index] = 1 ) )
RETURN
    IF (
        _Item IN { "A", "B", "C" },
        CALCULATE ( SUM ( 'Table'[QTY1] ), FILTER ( 'Table', 'Table'[Index] = 1 ) ),
        0
    )

QTY by Item = CALCULATE(SUM('Table'[QTY1]),FILTER('Table','Table'[Date1]))
QTY by Month = CALCULATE(SUM('Table'[QTY1]),FILTER('Table','Table'[MonthNumber]))

Result is as below.

 

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.