Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all
I'm trying to do a Gantt Chart and I'm almost there, but I still need to get a date difference between some dates to get the chart as I want.
Here's the example of what I want to do.
id Date
1 01/01/2019
1 01/20/2019
2 01/10/2019
1 02/20/2019
2 01/15/2019
2 01/20/2019
So I want to calculate two new columns which are these ones:
id Date Next Date Diff
1 01/01/2019 01/20/2019 19
1 01/20/2019 02/20/2019 31
2 01/10/2019 01/15/2019 5
1 02/20/2019 *Today() lots of days*
2 01/15/2019 01/20/2019 5
2 01/20/2019 *Today() lots of days*
For now I have this calculated column:
Solved! Go to Solution.
Hi, @AlexGlz
If I didn't misunderstand your meaning ,you can try the following steps:
Next Date01 =
var ind='Date'[Index]+1
var nextdate=CALCULATE(MAX('Date'[Date]),FILTER('Date','Date'[Index]=ind))
var nextid=CALCULATE(MAX('Date'[id]),FILTER('Date','Date'[Index]=ind))
var val=IF('Date'[id]=nextid,nextdate,TODAY())
return
val
DateDiff =
var days=DATEDIFF('Date'[Date],'Date'[Next Date01],DAY)
RETURN days
Here’s a sample I made:
Best Regards,
Eason
Community Support Team _ Eason Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @AlexGlz
If I didn't misunderstand your meaning ,you can try the following steps:
Next Date01 =
var ind='Date'[Index]+1
var nextdate=CALCULATE(MAX('Date'[Date]),FILTER('Date','Date'[Index]=ind))
var nextid=CALCULATE(MAX('Date'[id]),FILTER('Date','Date'[Index]=ind))
var val=IF('Date'[id]=nextid,nextdate,TODAY())
return
val
DateDiff =
var days=DATEDIFF('Date'[Date],'Date'[Next Date01],DAY)
RETURN days
Here’s a sample I made:
Best Regards,
Eason
Community Support Team _ Eason Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Unfortunately for now I couldn't make the index column because I'm taking the Data from a sharepoint and I don't know if the server has issues or something but I Power BI can't autenticate my credentials, so I can't add any column in power Query...
I will try again in the next days...
User | Count |
---|---|
98 | |
90 | |
78 | |
72 | |
65 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |