DAX - If statement with datediff based on most recent date

[cuseman03]

Hi all,

so I am pretty close to what I need to do but just stuck on this last part.  Ok, so I have two columns from SQL, one is a 'start date' and one is an 'end date'.  Format is datetime.  I used query editor and created a new calculated column that did a date diff in days.  Worked fine.  Every day between dates is accurate except for one, and this is a repeating pattern.  It is always wrong on the latest date for a given line item.  That is because in our warehouse, we denote record history with the latest having an end date '12/31/9999.

As you can see from the picture, DAX is counting the duration between the last record (start date is correct) but it's calculating to the above date.  What I want to do is instead say for the most recent record, in only this instance calculate days diff by subtracting 'today' from 'start date'

my logic is essentially this:  for every record except the latest, do X and for the latest do Y.   Also, how do I make it so 'day' count is only business days.

[cuseman03]

this worked great!

[andhiii079845 · Accepted solution]

Hi,
my solution:

 

 

DATEDIFF =
VAR _varstart = maxx(Tabelle11,Tabelle11[Start])
VAR _varend = maxx(Tabelle11,Tabelle11[End])
VAR _varmaxstart = maxx(ALLSELECTED(Tabelle11),Tabelle11[Start])
RETURN
IF(_varmaxstart=_varstart,DATEDIFF(_varstart,TODAY(),DAY),DATEDIFF(_varstart,_varend,DAY))