Calculate Number of Days Between Three Dates

[Stuznet]

Hi,
I’m scratching my heads how to write this logic.
In the data source, I have 3 dates. I want to write a datediff function between three dates.

Here is my logic
Date 1 minus the earlier Date2 or Date 3.

How do I write DATEDIFF based on this logic?

Thank you

[v-yulgu-msft · Accepted solution]

Hi @Stuznet ,

Based on my understanding, you want to calculate the days between [Date1] and the latested date from [Date2] and [Date3], right? In other words, if [Date2] is earlier than [Date3], you want the datediff between [Date1] and [Date3], otherwise, calculate the diff between [Date1] and [Date2].

Please refer to below measure.

Datediff =
DATEDIFF ( MAX ( [Date1] ), MAX ( MAX ( [Date2] ), MAX ( [Date3] ) ), DAY )

Best regards,

Yuliana Gu

[Stuznet]

@v-yulgu-msft 

Thank you so much!! 🙂 I wouldn't have figured it out.

[Anonymous]

Can you post some an example of what you would want? Not entirely clear

[Stuznet]

@Greg_Deckler  

I'm not sure I'm following

@Anonymous 

Here is the dummy report

[Greg_Deckler]

Depends on your data structure, but you want to get the MIN and the MAX and subtract those two. If these are in three columns, you can use my MC Aggregations Quick Measure to get the MIN and MAX of the three columns.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-...

[Stuznet]

Hi @Greg_Deckler 

So I followed your sample and created Max and I'm kinda lost. 

MC Max = 
VAR Date2 = SELECTCOLUMNS('Date',"Date",[Date2])
VAR Date3 = SELECTCOLUMNS('Date',"Date",[Date3])
VAR tmpTable = UNION(Date2,Date3)
VAR tmpValue = MAXX(tmpTable,[Date])
RETURN tmpValue

Then create another calculated column

DaysBetween = DATEDIFF('Date'[MC Max], [Date1], DAY)

 What am I doing wrong here?