Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Can you post some an example of what you would want? Not entirely clear
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |