Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Giavo
Helper III
Helper III

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
v-lili6-msft
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-lili6-msft
Community Support
Community Support

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.