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

[Giavo]

Hello all,

i have a Table1:
 

Product	Date1	Date2	Date3	Date4	Date5
A	01/01/2018	01/04/2018	28/06/2018	20/08/2018	28/08/2018
B	02/01/2018	24/03/2018	06/05/2018	31/05/2018	28/06/2018
C	03/01/2018	20/01/2018	21/01/2018	25/02/2018	22/04/2018
D	04/01/2018	27/03/2018	27/03/2018	29/03/2018	22/06/2018
E	05/01/2018	08/04/2018	16/06/2018	08/07/2018	07/08/2018
F	06/01/2018	13/04/2018	16/05/2018	12/08/2018	18/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

[v-lili6-msft · Accepted solution]

hi, @Giavo

After my research, you could try this way:

Step1:

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

Step2:

Close&Apply

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

 and drag Attribute field into slicer.

Result:

Best Regards,

Lin