Stacked Area Charts look distorted: Attempting to Fill Missing Periods with 0

[Anonymous]

I'm attemping to use the Stacked Area chart to show values over time, but occasionally some of my time periods are missing. This is causing the Stacked Area chart to look distorted and hard to read. 

I'd like to fix this by filling in a value of 0 for any missing periods, but I'm not sure if this is possible with DAX. 

Example: Some Periods Missing in the Data:

C1	1/1/2016	29
C1	4/1/2016	39
C1	5/1/2016	24
C1	6/1/2016	18
C1	9/1/2016	18
C1	10/1/2016	26
C1	11/1/2016	18
C1	12/1/2016	39
C2	1/1/2016	30
C2	2/1/2016	33
C2	5/1/2016	35
C2	6/1/2016	17
C2	7/1/2016	34
C2	8/1/2016	31
C2	11/1/2016	26
C2	12/1/2016	15

In this example, I'm missing data for several months. The chart becomes messy and difficult to read:

Switching the X-Axis to Categorical improves the situation, but I still have those weird blank areas and I'm missing a month:

If I can find a way to fill in all the missing periods with a 0 dynamically with DAX, I think everything will look a little better:

C1	1/1/2016	29
C1	2/1/2016	0
C1	3/1/2016	0
C1	4/1/2016	39
C1	5/1/2016	24
C1	6/1/2016	18
C1	7/1/2016	0
C1	8/1/2016	0
C1	9/1/2016	13
C1	10/1/2016	26
C1	11/1/2016	18
C1	12/1/2016	39
C2	1/1/2016	30
C2	2/1/2016	33
C2	3/1/2016	0
C2	4/1/2016	0
C2	5/1/2016	35
C2	6/1/2016	17
C2	7/1/2016	34
C2	8/1/2016	31
C2	9/1/2016	0
C2	10/1/2016	0
C2	11/1/2016	26
C2	12/1/2016	15

 My actual data has more categories of data. The more categories you have, the crazier things get:

It's difficult to show without dumping a ton of data into this post, but I'm working with many categories and groupings of observations, so each date period WILL be present somewhere in the data. A workaround I've considered is pivoting on the date column, replacing the nulls with zeros, and then unpivoting back. However, I don't think this is a sustainable solution. 

Thanks

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

After test, I think your solution is ok. For yorur requirement, you need to add the rows in your resource table, it is complex to achieve it. I use the following solution, please review.

1. Create a calendar table in Power Query, add C1 and C2 category.

let
    Source = List.Dates(#date(2016,1,1),366,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.Day([Column1])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.1", each "C1"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom.1", "C1"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "C2", each "C2"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom2", {"Column1", "Custom"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Date"}, {"Attribute", "Category"}})
in
    #"Renamed Columns1"

2. Create calculated column in Table1 and Calendar tables.

Column = Table1[Date]&Table1[Category]
Column = 'Calendar'[Date]&'Calendar'[Category]

3. Create relationship between the tables by calculated column.



4. Create a calculated column using RELATED function in Calendar table, you will use the Calendar to create the visual. Please download the attachments to test.



Best Regards,
Angelia