PowerBI Dynamic Time Series BarChart

2
votes

Adding on my previous question here: TimeSeries question

I would like to plot a unit capacity chart over a Time series (which contains a range of dates set by the user).

The chart I am trying to plot is as follows:

enter image description here

For each Unit Name, I have start and end date for the unit capacities, as shown in the PowerBI table as below:

enter image description here

4 sub questions:

  1. How to plot these capacities over time? Maybe using some DAX functions?
  2. Do i need the SSAS cube to solve this problem or can I do all the work inside PowerBI desktop? If not, is there a better way for example in SSRS?
  3. Is there a way to make the x-axis time series dynamic as specified by the user?

Adding to this, after Leonard's response. After converting the OutageStartDateOrig, and OutageEndDateOrig values I tried to create the calculated column as suggested in the youtube link {enter link description here}. However, the DAX formula as shown in the video gives out a syntax error for me stating that the '.' is incorrect when specifying the range of dates. Any ideas for this? [Screenshot below]:

enter image description here

1
sqlreporting-servicespowerbidax
If you want to add comments or additional information to the question/answer you posted in another question, you have to do it there instead of creating an additional question for that. – alejandro zuleta
sorry took a little long to write it, but there it is ;) Still a slightly different question but adds on it. Thanks! – Sambas23

1 Answers

2
votes

To create such a visual, I'd recommend an area chart (or stacked area chart) with the date on the axis, the unit name on the legend, and the capacity on the values. You could also do it as a stacked column chart too. However, then each date will be broken into discrete columns. See below image.

In terms of data manipulation, you'll need to convert the data with the date ranges you have above into a row for each individual date & unit. E.g. the first row, instead of being 11/2 to 13/2, would be expanded into 3 rows, one for each date.

You can do this in Power Query as you bring the data into Power BI Desktop, or in DAX after bringing it in. There are several solutions to this outlined in this thread (https://community.powerbi.com/t5/Desktop/Convert-date-ranges-into-list-of-dates/td-p/129418), but personally, I recommend the technique (and video) posted by MarcelBeug (https://youtu.be/QSXzhb-EwHM).

You'll also want an independent list of dates (with no gaps) to join the final date column to - otherwise your visual will skip dates when no units had capacity. By default, the chart will begin on the first date with data and end on the last date with data, so in that sense it is dynamic, but you can add a date slicer to give the end-user more control.

enter image description here

Area chart on top, column chart on bottom, date slicer on right filtering Jan-Mar.