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:

Item1Location1QTY1Date1
AX12.2.2023
CX12.2.2023
CY12.2.2023
B

Z

131.1.2023
AY231.1.2023
AX231.1.2023
CZ130.1.2023
CZ130.1.2023
AX330.1.2023
AY329.1.2023
BY129.1.2023
BZ129.1.2023
CX229.1.2023

 

So the desired output should be:

For example, if I want to see quantities per item for the entire year:

 2023
A1
B0
C2

 

Expected output for all items per date:

 29.1.202330.1.202331.1.20231.2.20232.2.2023
A33401
B20100
C22002

 

Or if only X location is in the filters per date:

 29.1.202330.1.202331.1.20231.2.20232.2.2023
A03201
B00000
C20001

 

Or if I only look at Z location per Month:

 JANFEB
Z10

 

I have tried using LASTNONBLANK() but it gives me the wrong values since my raw table doesn't include rows where the value is 0.
this was my try: 

CALCULATE (
SUM ( QTY1 ),
LASTNONBLANK (
'Table'[Date1],
CALCULATE (
COUNTROWS (  'QTY1' ),
ALLEXCEPT ( ' QTY1', 'Table'[Date1] )
)
)
)
 
Thank you for your help!
Need Help

5 Replies

    • mihzuk's avatar
      mihzuk
      Frequent Visitor
      2 years ago

      That works great until I put Item as a filter. Then it brings me back to latest QTY that I have in my table. 

       

      Example

       

      Latest value should be 0 since there is no QTY on 02.02.2023.
      Could you look into that?

       

      Thank you!

       

  • 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.