if date1 = date2 then shows as blank else shows date2

[Anonymous]

Hi,

Need some help in bulding the logic below.

I have 2 tables related by ID. When showing on a table format, I want a new column showing if DATE1 = DATE 2, then shows as blank, else shows DATE 2.

This is what I have today:

ID	Date 1 (Table 1)	Date 2 (Table 2)
1	01/02/2020	04/02/2020
2	02/02/2020	02/02/2020
3	03/02/2020	03/02/2020
4	04/02/2020	07/02/2020

And this is what I need:

ID	Date 1	New Column
1	01/02/2020	04/02/2020
2	02/02/2020
3	03/02/2020
4	04/02/2020	07/02/2020

Can somebody help me? Maybe a DAX formula with IF statemnt.

[Anonymous]

If the two tables are related by ID and this is 1-to-1 relationship, then you should merge them into one table in Power Query. Then it'll be easy to do what you want. Also in Power Query.

Best
D

[mahoneypat · Accepted solution]

If you have a table visual with ID and Date1 as columns, you could add a measure like this

Date2 Match = var date1value = selectedvalue(Table1[Date1])

var date2value = min(Table2[Date2]) //this assumes there is a single value in Table2 for this ID value

return if(datevalue1=datevalue2, blank(), datevalue2)

If there are multiple date values in Table2 for each ID, a different measure is needed and you'll need to describe the logic needed to get the desired Date2 value for comparison.

[Anonymous]

Hi @mahoneypat it worked like a charm :). Thank you a bunch.

In the same table I have also Amount 1 and Amount 2.

Is there any way I can create enrich these variables that if Date2value is blank then Amount2 is blank also?

I tried to use these variables you mentioned but couldn't do it.

[mahoneypat]

Yes.  Just reference the Date2 measure as follows:

Amount2 Measure = if(isblank([Date2Measure]), blank(), [Amount2 Measure])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Greg_Deckler]

Should be:

New Column =
  VAR __Date1 = 'Table1'[Date 1]
  VAR __Date2 = MAXX(RELATEDTABLE('Table 2'),[Date 2])
  IF(
    __Date1 = __Date2,__Date2,BLANK()
  )
    

You might also have luck with:

New Column =
  VAR __Date1 = 'Table1'[Date 1]
  VAR __Date2 = MAXX(FILTER('Table 2','Table 2'[ID] = 'Table 1'[ID]),[Date 2])
  IF(
    __Date1 = __Date2,__Date2,BLANK()
  )

Really going to depend on the data in your tables and their relationship to one another but you have left out some of those details.