Values Between Start and End Date Gantt Chart

[power_bi_123456]

I am trying to create a Gantt Chart type visual to show media spend for each campaign/brand. My table has start date, end date, campaign name, media spend. I also have a relationship through the start and end date to another date table. I want to use a matrix table to visualize spend for each campaign on a timeline view. My matrix table has campaign name, brand name in the rows and date year, date quarter, date month on the columns. I would like to know how to distribute spend across dates specifically by month.

Please let me know how to do this or any alternatives to create this visualization. I know there are gantt chart apps you can download but I would like to create this custom. Thanks.

[DataNinja777]

Hi power_bi_123456,

In my opinion, a gantt chart showing the duration of the campaigns horizontally cannot express the amount of the spending at the same time vertically, in two dimentional visual of horizontal and vertical gridlines of matrix while at the same time showing the campaign names on rows.  You maybe able to do that using the gantt chart custom visual which enables to show the names of the campaigns inside the gantt lines while at the same time, showing the amount in the vertical height and the durations using the horizontal lengths. In using the matrix table to express the duration as you required, I would write a measure like below:

Then, put that on the Values field of the Visualizations pane.  Then I used the conditional formatting on the measure value to make it blue colored.  

 I right clicked the Values field measure name, "Campaign duration" to set this conditional fomatting like below:

I am afraid that the resulting gantt chart using the matrix visual is not as professinoal looking like the custom visual one as it cannot express the names of the campaigns inside the gantt chart lines, and also, I couldn't put the gap between the gantt lines.  

Alternatively, you can try to express the campaign spending amount using the color conditional formatting instead of vertical height in the two dimentional matrix visualization.

I've tweaked the measure a bit to show the amount of "Media spend" instead of the number "1". 

Then I put the color conditional formatting.  

Similar to before, I used the right clicking of the measure name [Campaign duration] in the values field of the Visualization pane to get to the screen below for the conditional formatting.

If you would like, I can share the pbix file I prepared for this task.

[power_bi_123456]

Hi Sakiko, yes! Do you mind sharing the pbix file? I think this could work for our needs. 

[DataNinja777]

Hi @power_bi_123456,

Here is the link to the file on OneDrive.  

https://1drv.ms/u/s!AlqFfzVTqicpnV61lAqntI4s6kyr?e=LjwaBZ

Please let me know if you have any issue accessing it.  

Oh, one more thing... I didn't mention the important thing previously pertaining to this data model that the calendar table is a disconnected table in this data model. There should be no relationship between the fact table and the calender table (dimension table) for this data model to work properly.