cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 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] )
)
)
)

5 REPLIES 5
Community Support

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.

Super User

you need to create an unrelated table

you also need to create such a calendar Min and MAX

Super User

@mihzuk

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Frequent Visitor

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!

Super User

you checked my solution