sum latest value filtered by category and a date

[RaMiRo87]

Hi all,

I have a problem which might look very simple to most of you, but I don't come to a satisfying solution.

I have a fact table named 'InvoiceData' in which invoice data is recorded. I have individual prices per item [PurchPrice] and the total amount per item [LineAmount] in the table. Besides these values the fact table holds information about the order number, the item group, the purch unit (Kg, m, pcs etc.) and the price unit (1, 100, 1000 etc.). Now I would like to output the most recent data record for each item number in a table visual. As you can imagine, I have several data records per item number which all have a different booking date. The structure of the table visual should look like this and contain the latest record entry of each category:

Item Id	Max Date per Item Id	Max Order Nr. per Item Id	Max Item Group per Item Id	Max Purch Unit per Item Id	Max Price Unit per Item Id	Max Single Price per Item Id	Max Sum per Item Id
10050012	15.12.2023	PO-RG12345	Raw Materials	m	10	5	50
20070014	17.11.2023	PO-RG678910	Raw Materials	Kg	100	4	400

I have tried to set up various measures, but with the current measures I only get the largest entry per category, regardless of the filtering of item number and date.

Sorry if my english leads to misunderstandings, but it is not my first language. Please let me know on which bit of information I should be a little bit more precise.

Thanks in advance!

RR

[123abc · Accepted solution]

DAX Expression: Here's a DAX expression you can use to create a new table that contains the latest record for each item number:

LatestRecordTable =
SUMMARIZE(
InvoiceData,
InvoiceData[Item Id],
"Max Date per Item Id", MAX(InvoiceData[BookingDate]),
"Max Order Nr. per Item Id", MAX(InvoiceData[Order Nr.]),
"Max Item Group per Item Id", MAX(InvoiceData[Item Group]),
"Max Purch Unit per Item Id", MAX(InvoiceData[Purch Unit]),
"Max Price Unit per Item Id", MAX(InvoiceData[Price Unit]),
"Max Single Price per Item Id", MAX(InvoiceData[Single Price]),
"Max Sum per Item Id", SUM(InvoiceData[LineAmount])
)

1. Make sure to replace the column names with the actual column names from your "InvoiceData" table.

2. Use the New Table in Your Visual: Once you've created the new table, you can use it in your table visual. Drag the fields from the "LatestRecordTable" into the table visual to display the latest record for each item number.

This approach utilizes the SUMMARIZE function to group the data by the item number and calculate the maximum date, order number, item group, etc., for each item number.

Ensure that your data model is properly set up, and relationships between tables are defined correctly to enable accurate filtering and aggregation of data.

By following these steps, you should be able to create a table visual that displays the most recent data record for each item number, as per your requirements.

[v-huijiey-msft · Accepted solution]

Hi @RaMiRo87 ,

Hope everything is going well.

To display the latest data for each item id in the visual, please follow these steps:

1. Create a measure to find the latest date for each item ID.

Max Date per Item Id=
CALCULATE(
     MAX(InvoiceData[BookingDate]),
     ALLEXCEPT(InvoiceData, InvoiceData[ItemId])
)

2. Create a measure to get the latest OrderNumber for each item ID.

Max Order Nr. per Item Id=
CALCULATE(
     LASTNONBLANK(InvoiceData[OrderNumber], 1),
     FILTER(
         InvoiceData,
         InvoiceData[BookingDate] = [Max Date per Item Id] && InvoiceData[ItemId] = MAX(InvoiceData[ItemId])
     )
)

Max Item Group per Item Id, Max Purch Unit per Item Id, Max Price Unit per Item Id, Max Single Price per Item Id, Max Sum per Item Id also repeat the steps of Max Order Nr. per Item Id.

3. Drag the Item ID and the created measure onto the visual object for display.

4. Test in your test environment.

If you want to learn more about these functions, please refer to the following documentation:

ALLEXCEPT function (DAX) - DAX | Microsoft Learn

LASTNONBLANK function (DAX) - DAX | Microsoft Learn

If you have any questions please feel free to contact me and I would be grateful if you could provide me with detailed data for testing without personal privacy.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[RaMiRo87]

Thank you very much for your reply and the attached solution! Sorry for the late response. I've been very sick the last couple of weeks and didn't have the chance to take a glimpse at all the responses.

[123abc · Accepted solution]

DAX Expression: Here's a DAX expression you can use to create a new table that contains the latest record for each item number:

LatestRecordTable =
SUMMARIZE(
InvoiceData,
InvoiceData[Item Id],
"Max Date per Item Id", MAX(InvoiceData[BookingDate]),
"Max Order Nr. per Item Id", MAX(InvoiceData[Order Nr.]),
"Max Item Group per Item Id", MAX(InvoiceData[Item Group]),
"Max Purch Unit per Item Id", MAX(InvoiceData[Purch Unit]),
"Max Price Unit per Item Id", MAX(InvoiceData[Price Unit]),
"Max Single Price per Item Id", MAX(InvoiceData[Single Price]),
"Max Sum per Item Id", SUM(InvoiceData[LineAmount])
)

1. Make sure to replace the column names with the actual column names from your "InvoiceData" table.

2. Use the New Table in Your Visual: Once you've created the new table, you can use it in your table visual. Drag the fields from the "LatestRecordTable" into the table visual to display the latest record for each item number.

This approach utilizes the SUMMARIZE function to group the data by the item number and calculate the maximum date, order number, item group, etc., for each item number.

Ensure that your data model is properly set up, and relationships between tables are defined correctly to enable accurate filtering and aggregation of data.

By following these steps, you should be able to create a table visual that displays the most recent data record for each item number, as per your requirements.