PowerBI Time Series chart

0
votes

So I am trying to plot a value over a time series in powerBI report builder. I am currently getting the data from a relational MSSQL database. Now, this value (UnitCapacity) has a StartDate and an End Date. So what I have done is created a date time dimension inside powerbi using an mquery to replicate the days between a particular year and another. What I am trying to do is to plot the Unit capacities over a time series chart. Then I created filters so that I can choose which Refinery unit to plot.

So how I tried to tackle it is by creating a relationship between the IIROutagesDenormalised and DateTimeDim over the handle where the handle is in this format: {YYYY}-{MM}-{DD}. Is this the right way to do this please?

When I tried to Create the DAX query to get the Calendar date dimension, this is giving me the error below:

enter image description here

1
sql-serverpowerbidax

1 Answers

2
votes

You don't need to take care of the date format because it should be handled by Power BI, as long as the data type is correct. Not sure about the business logic but there is a simpler way using DAX.

You can create a calendar table using DAX:

DateTimeDim = CALENDAR(MIN(IIROutagesDenormalised[OutageStartDate]), MAX(IIROutagesDenormalised[OutageEndDate]))

Which returns a table with column Date.

date

If you create a relationship between the Date column and OutageStartDate: relationship

With a simple measure (depending on the business logic), like 

Total = SUM(IIROutagesDenormalised[UnitCapacity])

You can plot something like the following:

line chart

Which also works with the filter:

line chart 2