cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Giavo Regular Visitor
Regular Visitor

DAX: Dynamically change columns in Gantt chart's end date

Hello all,

i have a Table1:
 

ProductDate1Date2Date3Date4Date5
A01/01/201801/04/201828/06/201820/08/201828/08/2018
B02/01/201824/03/201806/05/201831/05/201828/06/2018
C03/01/201820/01/201821/01/201825/02/201822/04/2018
D04/01/201827/03/201827/03/201829/03/201822/06/2018
E05/01/201808/04/201816/06/201808/07/201807/08/2018
F06/01/201813/04/201816/05/201812/08/201818/09/2018

 

 

I have created a Gantt chart:
As you already know, It requires a Start date and Duration=DATEDIFF(Date Start, Date End) in the Duration field.
In my formula the Start Date is always "Date1" column while the End date must be dynamic (so depending on the selection i want the user to decide if the End Date is Date2, Date3, Date4 or Date5). How can i dynamically calculate this duration with a fixed Start Date (Date 1 column) and dynamic End Date column ? so Duration=DATEDIFF( Date1, DateX)

 

Thank you all in advance

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX: Dynamically change columns in Gantt chart's end date

hi, @Giavo

After my research, you could try this way:

Step1:

In Edit Queries, Select Date2, Date3, Date4, Date5 then click Transform->Unpivot Columns

5.JPG6.JPG

Step2:

Close&Apply

Then you could use Value as End date or use Date1 and Value to get Duration Duration=DATEDIFF(Date1, Value, DAY) 

7.JPG

 and drag Attribute field into slicer.

 

Result:

8.JPG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: DAX: Dynamically change columns in Gantt chart's end date

hi, @Giavo

After my research, you could try this way:

Step1:

In Edit Queries, Select Date2, Date3, Date4, Date5 then click Transform->Unpivot Columns

5.JPG6.JPG

Step2:

Close&Apply

Then you could use Value as End date or use Date1 and Value to get Duration Duration=DATEDIFF(Date1, Value, DAY) 

7.JPG

 and drag Attribute field into slicer.

 

Result:

8.JPG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.