Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
Jubari
New Member

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

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?

Jubari_0-1725285530494.png

 

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

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

 

View solution in original post

3 REPLIES 3
selimovd
Super User
Super User

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

 

Thank's. But one correction end with _Result

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.