Value on last date

[mihzuk]

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
C	Y	1	2.2.2023
B	Z	1	31.1.2023
A	Y	2	31.1.2023
A	X	2	31.1.2023
C	Z	1	30.1.2023
C	Z	1	30.1.2023
A	X	3	30.1.2023
A	Y	3	29.1.2023
B	Y	1	29.1.2023
B	Z	1	29.1.2023
C	X	2	29.1.2023

So the desired output should be:

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

	2023
A	1
B	0
C	2

Expected output for all items per date:

	29.1.2023	30.1.2023	31.1.2023	1.2.2023	2.2.2023
A	3	3	4	0	1
B	2	0	1	0	0
C	2	2	0	0	2

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

	29.1.2023	30.1.2023	31.1.2023	1.2.2023	2.2.2023
A	0	3	2	0	1
B	0	0	0	0	0
C	2	0	0	0	1

Or if I only look at Z location per Month:

	JAN	FEB
Z	1	0

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!

[v-weiyan1-msft]

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.

[Ahmedx]

you need to create an unrelated table

you also need to create such a calendar Min and MAX

[Fowmy]

@mihzuk 

Check the attached PBI file for the meaure and the data model:

[mihzuk]

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!

[Ahmedx]

you checked my solution