Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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:

 

C11/1/201629
C14/1/201639
C15/1/201624
C16/1/201618
C19/1/201618
C110/1/201626
C111/1/201618
C112/1/201639
C21/1/201630
C22/1/201633
C25/1/201635
C26/1/201617
C27/1/201634
C28/1/201631
C211/1/201626
C212/1/201615

 

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

 

 

g2.PNG

 

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

 

 

g2_cat.PNG

 

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:

 

C11/1/201629
C12/1/20160
C13/1/20160
C14/1/201639
C15/1/201624
C16/1/201618
C17/1/20160
C18/1/20160
C19/1/201613
C110/1/201626
C111/1/201618
C112/1/201639
C21/1/201630
C22/1/201633
C23/1/20160
C24/1/20160
C25/1/201635
C26/1/201617
C27/1/201634
C28/1/201631
C29/1/20160
C210/1/20160
C211/1/201626
C212/1/201615

 

g2_filled.PNG

 

 

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

d1.PNG

 

 

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

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

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.

1.PNG

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.

2.PNG

Best Regards,
Angelia

 

View solution in original post

1 REPLY 1
v-huizhn-msft
Employee
Employee

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.

1.PNG

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.

2.PNG

Best Regards,
Angelia

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.