PowerBI Time Series chart

问题:

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:

回答1:

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.

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

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

Total = SUM(IIROutagesDenormalised[UnitCapacity])

You can plot something like the following:

Which also works with the filter: