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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
NMC20
Frequent Visitor

LOOKUP with conditions

I have two tables and I would like a lookup from one to the other based on a condition. 

I have a "Sites Information" table which shows me [Location] [Installation Date] [% first 3 months] [3 months date] [% after 3 months] per venue we have

I have an "All Payment Data" table which has the [Location] and [Date of Order] and provides individual orders taken and which site this can be attributed to

I've managed to do a simple lookup between [Location] to bring back all of the fields in "Sites Information" in "All Payment Data". However, I would like a column in "All Payment Data" which tells me what % revenue from each order should be going to the venue based on the date of the order:

 

If [Date of Order] is (>= [Installation Date] and < [3 months date]) then [% first 3 months]

If [Date of Order] is >= [3 months date] then [% after 3 months]

 

Could anyone advise? Thanks!

1 ACCEPTED SOLUTION
Poojara_D12
Super User
Super User

Hi @NMC20 

 

  • Go to the "All Payment Data" table in Power BI.
  • Create a new calculated column by clicking on the Modeling tab and selecting New Column.
  • Use the following DAX formula:

 

% Revenue =
VAR InstallationDate = RELATED('Sites Information'[Installation Date])
VAR ThreeMonthsDate = RELATED('Sites Information'[3 months date])
VAR First3MonthsPercentage = RELATED('Sites Information'[% first 3 months])
VAR After3MonthsPercentage = RELATED('Sites Information'[% after 3 months])
RETURN
    IF(
        [Date of Order] >= InstallationDate && [Date of Order] < ThreeMonthsDate,
        First3MonthsPercentage,
        IF(
            [Date of Order] >= ThreeMonthsDate,
            After3MonthsPercentage,
            BLANK() -- Or use 0 or any default value if needed
        )
    )
​

Assuming :

 

  • Your "Sites Information" and "All Payment Data" tables are correctly related via the [Location] column in a one-to-many relationship.
  • The date fields in both tables are in a valid date format.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

View solution in original post

2 REPLIES 2
Kedar_Pande
Super User
Super User

@NMC20 

Create new column in the "All Payment Data" table:

Revenue Percentage = 
IF(
'All Payment Data'[Date of Order] >= RELATED('Sites Information'[Installation Date]) &&
'All Payment Data'[Date of Order] < RELATED('Sites Information'[3 months date]),
RELATED('Sites Information'[% first 3 months]),
IF(
'All Payment Data'[Date of Order] >= RELATED('Sites Information'[3 months date]),
RELATED('Sites Information'[% after 3 months]),
0
)
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

 

Poojara_D12
Super User
Super User

Hi @NMC20 

 

  • Go to the "All Payment Data" table in Power BI.
  • Create a new calculated column by clicking on the Modeling tab and selecting New Column.
  • Use the following DAX formula:

 

% Revenue =
VAR InstallationDate = RELATED('Sites Information'[Installation Date])
VAR ThreeMonthsDate = RELATED('Sites Information'[3 months date])
VAR First3MonthsPercentage = RELATED('Sites Information'[% first 3 months])
VAR After3MonthsPercentage = RELATED('Sites Information'[% after 3 months])
RETURN
    IF(
        [Date of Order] >= InstallationDate && [Date of Order] < ThreeMonthsDate,
        First3MonthsPercentage,
        IF(
            [Date of Order] >= ThreeMonthsDate,
            After3MonthsPercentage,
            BLANK() -- Or use 0 or any default value if needed
        )
    )
​

Assuming :

 

  • Your "Sites Information" and "All Payment Data" tables are correctly related via the [Location] column in a one-to-many relationship.
  • The date fields in both tables are in a valid date format.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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