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

[Jubari]

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?

[selimovd · Accepted solution]

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

[Jubari]

Thank's. But one correction end with _Result

[selimovd]

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