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

[VictorR]

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?

 Table1 to Table2Table2 to Table3

Table3 to Table4

Highlighted 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).

[praiselyabraham · Accepted solution]

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 

Hope this solves your issue.

Regards.

[VictorR]

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

[Anonymous]

Would something as simple as this work for you:

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

[VictorR]

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!

[Seward12533]

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.