Flat sparkline if no data exists

[pat1214]

Hello,

I am building a dashboard which consists of different metrics. Most of them have data for past two years, except for three metrics that have data for the past 8 months only. One of the displays on the dashboard is a sparkline visual which shows a 13 month trend. I have attached a snapshot of two metrics as an example. The end user wants the months for which there is no data to be flat instead of starting from July 2018. There is no data available for the metric before Jul 2018 in the underlying data model either. I am not sure how to solve this. Should this be taken care of in the data model / underlying table (for example, inserting NULL values for the missing months) or in the dashboard in PowerBI? Any help is greatly appreciated. This is my first project using a visualization tool and I could really use some direction. Thank you!

[v-frfei-msft]

Hi @pat1214 ,

Here you should create a calendar table using the formula like table = CALENDARAUTO() and create relationship between it and your fact tables. Then you can add the date column in calendar table in your visual to work on it.

Regards,

Frank

[pat1214]

Hello,

Thank you for the reply. I should have mentioned in my original post that there is a column called 'Reporting Period' in the staged table that populates the dashboard. I have attached a picture of it below as an example. 15 and 16 are metric ids. Metric 15 for example has data all the way from Oct 2016 to January 2019, but Metric 16 has data from Oct 2016 to Aug 2018. So in the sparkline visual, I am trying to get a flat line for months Sept 2018 to Jan 2019. Will inserting 'Null' for the missing values in the staged table help in achieving it? Thank you!