Line and stacked column chart – displaying all months when using legend

[Pawel_J]

Hello,

In my sample data model I have 2 tables. First table contains information about sales, the second table is a date table created using DAX. 

I’m currently facing an issue with my line and stacked column chart. I’m trying to display the monthly sales amount in columns and the running total in a line. For the first scenario the visual is working correctly and shows all the months of the year on the x-axis, even if there were no sales in a specific month:

The situation changes when I am adding a column legend, the months with no sales disappear:

I’m looking for a solution that allows me to include the column legend without losing the display of months with zero sales.

Below I am sending the sample sales table:

Company	Transaction date	Fruit name	Fruits sold
A	11/10/2022	Banana	4
A	11/10/2022	Apple	2
A	12/5/2022	Apple	1
A	1/9/2023	Apple	3
B	1/9/2023	Banana	3
A	1/10/2023	Banana	2
A	1/20/2023	Apple	4
B	2/2/2023	Apple	12
A	2/28/2023	Apple	2
B	3/17/2023	Banana	3
B	4/13/2023	Apple	4
A	4/26/2023	Apple	6
A	5/1/2023	Apple	3
A	6/7/2023	Apple	8
A	6/7/2023	Apple	3
A	7/13/2023	Apple	2
B	8/21/2023	Apple	2
A	10/30/2023	Apple	5
A	11/10/2023	Banana	4
A	12/5/2023	Apple	1

Here is the date table created using DAX:

Date = 
VAR MinYear = YEAR ( MIN ( 'Fruits sales'[Transaction date] ) )
VAR MaxYear = YEAR ( MAX ('Fruits sales'[Transaction date] ) )
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ),
        AND ( YEAR ( [Date] ) >= MinYear, 
              YEAR ( [Date] ) <= MaxYear )
    ),
    "Year", YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Quarter", FORMAT( [Date], "\QQ" )
)

Here is the relationship:

Here is the running total formula:

Running Total = 
CALCULATE (
    SUM('Fruits sales'[Fruits sold]),
    DATESYTD( 'Date'[Date] )
)

Any help or guidance would be greatly appreciated.  

[Pawel_J]

Thank you very much for your responses. In my case I was using a measure only to calculate the running total and "Show items with no data" worked.

[v-yangliu-msft · Accepted solution]

Thanks for the reply from @Ritaf1983 , please allow me to provide another insight: 
Hi  @Pawel_J ,

You can click X-axis - [Month Name] - Show items with no data.

Best Regards,

Liu Yang

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

[Ritaf1983]

Hi @Pawel_J 
You can add 0 to the measure of bars.
For example (from my sample file) :

Total sales (K/$) = SUMX('orders',('orders'[Unit price]- [Discount])*'orders'[Quantity])+0

The pbix is attached

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