SUM with LASTDATE independent from other columns

[Krzysztof_Hyla]

Hi,  

I have following inventory history table with three columns: report date, warehouse location & inventory value:

I'm trying to present only current inventory state.

The following formula calculates correctly currrent inventory value (sum of rows for the last report date):

Current Inventory Balance =

CALCULATE(

SUM('Warehouse History'[Value]),

LASTDATE('Warehouse History'[Date])

)

But when I'm trying to replace [Value] column with it, the table is still showing unwanted rows, though the sum is ok:

Any idea how to remove them, to achieve this result:?

Here is the link to pbix file - hope it works.

[v-yalanwu-msft · Accepted solution]

Hi, @Krzysztof_Hyla ;

Please try it.

Current Inventory Balance = 
IF(MAX([Date])=CALCULATE( LASTDATE('Warehouse History'[Date]),ALL('Warehouse History')),
CALCULATE(                                     
  SUM('Warehouse History'[Value]),
  LASTDATE('Warehouse History'[Date])))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Krzysztof_Hyla]

This gave me exactly what I needed - thanks a lot!

[Krzysztof_Hyla]

I have used this measure, but no luck so far:

 Here is the exact code I've used:

Amitchandak Measure =

VAR __id = MAX ('Warehouse History'[Location] )

VAR __date =

CALCULATE (

MAX('Warehouse History'[Date] ),

ALLSELECTED ('Warehouse History' ),

'Warehouse History'[Location] = __id )

RETURN

CALCULATE (

SUM('Warehouse History'[Value]),

VALUES ('Warehouse History'[Location] ),

'Warehouse History'[Location] = __id,'Warehouse History'[Date] = __date

)

I need to somehow hide the first 3 rows.

[amitchandak]

@Krzysztof_Hyla , Try a measure likethis for current inventory

Measure =
VAR __id = MAX ('Warehouse History'[Location] )
VAR __date = CALCULATE ( MAX('Warehouse History'[Date] ), ALLSELECTED ('Warehouse History' ), 'Warehouse History'[Location] = __id )
CALCULATE ( SUM('Warehouse History'[Value]), VALUES ('Table'[Location] ),'Warehouse History'[Location] = __id,'Warehouse History'[Date] = __date )