Compare Dates to get earliest

DAX DAX Calculations
1

Hi all,

In my file, I have a table called Campaign Member Create Date with the following date fields:

Event List Member Create Date, Campaign Email Event Date & Form Submit Date.
I want to create a separate column that retrieves the earliest date out of those three.

Should I use a nested if or switch or is there a better way. Thank you for your help.

Also, how can this be accomplished in Query?

image
image784Ă—130 3.3 KB

PPD Prospects Dashboard.pbit (738.5 KB)

2

Hello,

I can’t open your PBIT file as it is connecting to data and want authentication but for overall logic you can try something like this


Earlierst date =

var _date1 = ROW("_Date", [Create Date])
var _date2 = ROW("_Date", [Campaign Email Event Date])
var _date3 = ROW("_Date", [Form Submit Date])
var _earliestDate = MINX(UNION(_date1,_date2,_date3), [_Date])

return

_earliestDate

But I used the same column names you have mentioned so hopefully it will work out of the box

3

You are a genius…

Can you explain the syntax. I have never used it before.

thanks sir. Trying to learn Dax better

4

When we are working with tables and calculating columns, I can grab into variables their respective values for a given row just by referring to it’s name. But for my minx formula I need a table to iterate through.

So what I did is create 3 mini tables with 1 row and 1 column with date from each of the tables. Like in example below

obraz

Then I used union function. It returns 1 table with 3 rows so I can iterate through it.

obraz

Then just using minx function to get my min date. If you want to do it in power query you can use something like this

obraz
obraz699Ă—438 10.3 KB

Just change column names for the one that you have

3 Likes
5

Wow. Just unbelievable.

I was trying to do this complicated nested if statement and kept giving me “Expressions that yield variant data type cannot be used to define calculated columns” even though my data types were the same.

Your solution bypassed all that headache.

I see I need to learn and use Dax query. I hardly use it.

I really appreciate it.

6

Out of curiosity. What would the syntax be to get the most recent date?

7

I would advise to start using dax studio. It helps you visualise the tables you are working with and if you want to unlock full power of “X” functions, you have to have a good grasp how your virtual tables looks like when you iterate through them.

Change min to max :wink:

1 Like