Target Line on Chart not displaying across whole Year

[ammcleod]

I have a "Line and Stacked Column Chart" with data spread across a couple years. I have created a Cumulative line to display the counts for each month as a cumulative value. 

I have a table with target values for each year but when I add the measure to the graph it appears as dots and lines instead of going across the whole year.

 This is what the current chart looks like. The Red line is the Target Line which I am trying to get to extend across the whole year, even if there is no data. 

The above image is the table I have with the target values for each year. 

I am thinking that the target line isn't working properly because I don't have all the dates in a month? or that because there is no data for that month and I want to show items with no data it's chopping out my target line as well.. ? 

Any help would be appreciated!

[v-alq-msft · Accepted solution]

Hi, @ammcleod 

I'd like to suggest you create a date table and join it with the fact table. Then you may use the data column from date table as the axis.

Typically, your data model will contain a calendar table. In fact, it is usually better to aggregate data by year and month using columns of a calendar table (containing one row for each day) instead of extracting the date parts from a single column of type date or datetime in calculated columns. There are a few reasons for this choice. You obtain a model wherein all date attributes are included in a separate table making it easier to browse data using a generic client, and you can use special DAX functions that perform time intelligence calculations. Moreover, most of the time intelligence functions require a separate Date table to work correctly.

You may use calendar or calendarauto function to create a date table. For further information, please refer to the following links.

https://docs.microsoft.com/en-us/dax/calendar-function-dax

https://docs.microsoft.com/en-us/dax/calendarauto-function-dax

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pragati11]

Hi @ammcleod ,

Create a Calendar Date table in Power BI and link it on date column in your table.

Then on your chart use the Date column from Calendar date table as X-Axis.

Also, if there is missing data in your table for some dates, it will not be populated on the chart. 

Thanks,

Pragati