line chart with dates and missing data

[ms92ita]

Hi,

I am getting some troubles on a report with direct query connection from a SQL server.
In this report i have a slicer of dates (between) and some charts/counters. Specifically I have a line chart that has no data in the highlighted part in the following screenshot:

The correct value would be 0 if not exists, but the line is continuous and this is not correct.
Creating a table with continuous dates and values is not an option because these graphs will be available for many customers from the same DB, so in a SQL server side the size would be big. Is there another mode or free graph that removes this error on a timeline?

[ms92ita]

As you can see, the granularity is per day and not aggregated... The visual is for a year.

[negi007]

@ms92ita can you share your pbix file or sample data in table format which can be copied

[ms92ita]

PBIX is a bit complex to export beacuase the the chart a little part of a bigger report and I do not know how to export data from a calculated table. Anyway, the structure of the source data for time line is this:

X axis is date, while Items and TotalItems are line values

Another example is from FirstDate and LastDate as y-axis:

[v-jingzhang]

Hi @ms92ita

Please create the following measures for Items and TotalItems. They will return 0 for blank values. Use these measures for line values to replace the original columns. 

M_Items = SUM ( 'TableName'[Items] ) + 0

M_TotalItems = SUM ( 'TableName'[TotalItems] ) + 0

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ms92ita]

Hi @v-jingzhang 

An additional measure is not the solution because the original table does not have a "continuous" value for dates, so in the summarization some dates would miss for sure.
Since that a table with continuous values for all customers will be massive and not optimized, can I link a "table" to a stored procedure that gets data from filters that I will send like customerid and min/max dates selected from a range? With that, from the database I can recreate a continuous time series.

[v-jingzhang]

Hi @ms92ita 

To have a continuous date series, you can add a Date table. A simple method is to use DAX function Calendar. Then build a relationship between Date table and Fact table on Date column. Use Date table's Date column on X-axis of the line chart. This could make the X-axis have all continuous dates. And on the dates that are missing in Fact table, the measures with "+ 0" will return 0. 

The following articles explain why you need a Date table and how to create a Date table with DAX:

Do You Need a Date Dimension? - RADACAD

Creating a simple date table in DAX - SQLBI

Creating a simpler and chart-friendly Date table in Power BI - SQLBI

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[negi007]

@ms92ita 

It seems values are aggregating at month level. Please try to drill down one more level to date level