topic Re: Lookup data to New column with data from another table filtered by date in DAX Commands and Tips

Lookup data to New column with data from another table filtered by date

Jubari — Mon, 02 Sep 2024 14:00:42 GMT

Hi Guys,

Heres a tricky one.

Background:

I'm creating a Model to be shared within a Group and as usual no-one within the Group has the data in same format. But the result of my creation needs to be in a "standard format"

The result must be a model so that it can be linked to a report where the group data is combined.

I've tried quite a few combinations trying to filter the result that I need but I am failing. Any suggestions hot to get the result I need?

Re: Lookup data to New column with data from another table filtered by date

selimovd — Mon, 02 Sep 2024 14:30:18 GMT

Hey Jubari ,

as I understood you want that as a calculated column.

Give this a try:

Cost = VAR _ArticleThisRow = 'Sales Data'[Article Number] VAR _SalesDate = 'Sales Data'[Sales Date] VAR _Result = CALCULATE ( MAX ( 'Cost Price'[Price] ), 'Cost Price'[Article Number] = _ArticleThisRow && ( _SalesDate >= 'Cost Price'[Start Date] && _SalesDate <= 'Cost Price'[End Date] ) ) RETURN Result

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Connect with me: LinkedIn

Re: Lookup data to New column with data from another table filtered by date

Jubari — Tue, 03 Sep 2024 06:27:31 GMT

Thank's. But one correction end with _Result

Re: Lookup data to New column with data from another table filtered by date

selimovd — Tue, 03 Sep 2024 11:39:12 GMT

You are absolutely correct Jubari . Sorry about that, just wrote it down without testing, but yes, should be RETURN _Result at the end.
I'm happy the solutions works.

Best regars

Denis