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.
I am trying to calculate the datediff between Date1 and Date2 fields. Please note that Date2 column has values that are -1
If the date2 <0 , substitute the date2 value to today() before calculating "Difference" value.
Any help is appreciated.
Date 1 | Date 2 | Difference |
8/1/2019 | 1/1/2022 | |
9/1/2019 | 2/1/2022 | |
10/1/2019 | 3/1/2022 | |
11/1/2019 | 4/1/2022 | |
12/1/2019 | 5/1/2022 | |
1/1/2020 | 6/1/2022 | |
2/1/2020 | 7/1/2022 | |
3/1/2020 | 8/1/2022 | |
4/1/2020 | -1 | |
5/1/2020 | -1 |
Solved! Go to Solution.
@Anonymous , I Power Query Replace them with Date.From(DateTime.FixedLocalNow()))
First replace -1 with null
then change datatype to date and then create a new column
if [Date2] = null then Date.From(DateTime.FixedLocalNow())) else [Date2]
Then you can use datediff in DAX
DAX
Datediff([Date1], [Date2], day)
or
Datediff([Date2], [Date1], day)
@Anonymous , I Power Query Replace them with Date.From(DateTime.FixedLocalNow()))
First replace -1 with null
then change datatype to date and then create a new column
if [Date2] = null then Date.From(DateTime.FixedLocalNow())) else [Date2]
Then you can use datediff in DAX
DAX
Datediff([Date1], [Date2], day)
or
Datediff([Date2], [Date1], day)
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 |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |