cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VictorR Frequent Visitor
Frequent Visitor

How to create a custom column that compares dates from two different tables in one of the tables?

Hi,

 

I have 4x different tables loaded into PowerBI Desktop. Table1 has a Many To One relationship to Table2 which has a Many To One relationship to Table3 which has a One To One relationship with Table4.

 

My mission is to try and create a custom column in Table1 that has a number 1 or 0, if the date in Table1 is after the date in Table4.

 

Can anyone please help me out here?

 

 1.jpgTable1 to Table22.jpgTable2 to Table3

3.jpgTable3 to Table4

4.jpgHighlighted Date-Time fields that need to be compared

Please let me know if there is any extra information you would like?

 

Note: the date fields mentioned are in the format of date-time (dd/mm/yyyy hh:mm:ss am/pm).

1 ACCEPTED SOLUTION

Accepted Solutions
praiselyabraham Regular Visitor
Regular Visitor

Re: How to create a custom column that compares dates from two different tables in one of the tables

Hi ,

Based on the scenario, this is what i did as a replicate of your scenario.

Then created a column in table one as 

 

Date Check Flag = IF([Date]> RELATED(Table4[Date]),1,0)

 

Alternatively you can create two column in table as 

Table4 Date = RELATED(Table4[Date])

Check Flag = IF([Date]>[Table4 Date],1,0)

 

This gives the required flag for you 

 

Capture1.PNG

 

Capture.PNG

 

Hope this solves your issue.

 

Regards.

5 REPLIES 5
Seward12533 New Contributor
New Contributor

Re: How to create a custom column that compares dates from two different tables in one of the tables

DateCheck = [datefieldtable1]>CALCULATE(AVERAGE(Table4[datefield]),Table1)  You should have and may need a Calendar Table where they Key Date fields from 1,3 and 4 respectively link to the the key date in your date table. 

 

This should work, specifying the other table in the calculate will force the filter context from Table 1 to be enforced based on the relationships.  Don't know your data model well enough to know if the relationships you have defined will be sufficent. 

Super User
Super User

Re: How to create a custom column that compares dates from two different tables in one of the tables

Would something as simple as this work for you:

 

Your Column = IF(
	[ufv_time_in] < MIN('vsl_vessel_visit_details'[cargo_cutoff]),
	1,
	0
)

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


praiselyabraham Regular Visitor
Regular Visitor

Re: How to create a custom column that compares dates from two different tables in one of the tables

Hi ,

Based on the scenario, this is what i did as a replicate of your scenario.

Then created a column in table one as 

 

Date Check Flag = IF([Date]> RELATED(Table4[Date]),1,0)

 

Alternatively you can create two column in table as 

Table4 Date = RELATED(Table4[Date])

Check Flag = IF([Date]>[Table4 Date],1,0)

 

This gives the required flag for you 

 

Capture1.PNG

 

Capture.PNG

 

Hope this solves your issue.

 

Regards.

Highlighted
VictorR Frequent Visitor
Frequent Visitor

Re: How to create a custom column that compares dates from two different tables in one of the tables

Thank you very much! It was the "RELATED" function that solved this and gave me the expected results I was looking for!

VictorR Frequent Visitor
Frequent Visitor

Re: How to create a custom column that compares dates from two different tables in one of the tables

Unfortunately this did not work for me, but another user posted a solution that did work, the use of the "RELATED" function was what this needed!