Dax measure with lastdate and filters

[Anonymous]

I currently have a table containing information about a customer's inventories.

My purpose is to build a measure that calculates the reported inventory in units based on the last date of Inventory Date per client.

Table with information

Client Name	Product Code	Stock in units	Inventory Date
AA	001	3	1/01/2023
AA	001	4	5/02/2023
BB	001	6	13/02/2023
BB	002	4	5/02/2023

Expected outcome

Client Name	Product Code	Last Inventory Date by client	Last stock based on Inventory Date per client
AA	001	5/02/2023	4
BB	001	13/02/2023	6
BB	002	13/02/2023	0

In short, I need the measure to take the last date reported by each client and check if by that date there is inventory information or not.

I am currently handling this formula, but it does not work as desired, because it calculates the last inventory for each product code. And what I need is that regardless of the product code, check if on the last date there is inventory or not

I hope you can please help me to correct this formula or to reformulate it.

SUMX(ALL(Table[Client Name]),CALCULATE(SUM(Table[Stock (in units)]),LASTDATE(Table[Inventory Date])))

[FreemanZ]

hi @Anonymous 

try to create a table visual with Client Name column and two measures like:

Last Inventory Date =

MAX(TableName[Inventory Date])

Last stock  =

VAR _date = [Last Inventory Date]

RETURN

MAXX(

   FILTER(

        TableName,

        TableName[Inventory Date]=_date

    ),

    TableName[Stock in units]

)

[Anonymous]

Hi @FreemanZ 

Thanks for taking the time to review my question.

The formula works fine when I create a visual with exclusively the Client name. However, I need the visual with both columns, Client name and product code. I tried your measure and it happens the same that it does with mine, when I put both columns, it gives me the last inventory date per product code.