Power BI - Show zero/null value on line chart with dates

Question

I'm using a simple table like this to make a report in Power BI:

Order number	Order date	Turnover
001	30/1/2022	10 €
002	30/1/2022	20 €
003	2/2/2022	15 €

I need to create a line chart showing all the dates, even where I have no data (no orders for that day). This is currently how is shown:

You can notice that the 1/2/22 and 3/2/22 are missing due to no order, but I want them to be visible and the value should be 0. This is also affecting the average line because it's calculated based on the days with data, but I need to put into account also the 0-turnover days.

I tried to use the "Show items with no data" on the date dimension and switch the X axis from Continuous to Catergorical and the other way around. I also tried to create a new metric like this:

Total Turnover = IF(ISBLANK(SUM(Orders[Turnover (EUR)])), 1, SUM(Orders[Turnover (EUR)]))

but it's not working.

Answer 1

If I understand your business requirement correctly, you are going to need to do three things:

1. Make sure you have a date-dimension table in your model. Build the relationship based on your [Order date] column.
2. Refactor your [Total Turnover] measure as such:

Total Turnover = 

VAR TotalTurnover = SUM( Orders[Turnover] )
RETURN

IF(
    ISBLANK( TotalTurnover ),
    0,
    TotalTurnover
)

3. Build your line chart using the [Date] column from your date table.