Forum Discussion

VictorR's avatar
VictorR
Frequent Visitor
8 years ago
Solved

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?

 

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

Need Help
  • praiselyabraham's avatar
    praiselyabraham
    8 years ago

    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.

5 Replies

  • praiselyabraham's avatar
    praiselyabraham
    Icon for Resolver II rankResolver II
    8 years ago

    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's avatar
      VictorR
      Frequent Visitor
      8 years ago

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

  • Seward12533's avatar
    Seward12533
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      VictorR
      Frequent Visitor
      8 years ago

      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!