missing data

[Anonymous]

Hi All

I have sales data per customer which I would like to display in line chart for whole year comparing YOY sale. 

Majority of the customers do not place orders every day or  sometimes for the whole week.

Is there a way to add, for the missing weeks, zero value so that the line chart will be one uninterrupted line 

below my sales data sample

thank you 

 

[v-caliao-msft · Accepted solution]

@Anonymous,

You could create a date table in your data model, and then cretae ralationship between your original table and this date table. Here is the sample DAX expression to create a date table.
Date =CALENDAR (DATE(2000;1;1); DATE(2025;12;31))

Please refer to the link below to see the details.
https://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

Regards,

Charlie Liao

[David_Schrag]

I have the same kind of issue as the original poster. I have many months of customer purchase data, which I have aggregated by quarter (2016-1, 2016-2, 2016-3, etc.). I want to create a line chart visualization that displays every quarter between 2015-1 and 2017-4, including quarters where the total purchases were zero. But I don't have any records in my database when there were no purchases.

I can easily create a table that contains a record for every quarter in the time period, but how would I create a relationship between that table and my purchase records table that would force a zero value to display for a given quarter when there are no matching purchase records? None of the join options seem to do this.

[Ashish_Mathur]

Hi @David_Schrag,

Share a dataset and show the expected result.

[David_Schrag]

This is highly oversimplified, but imagine the data is something like this:

Date         InvoiceAmount        Quarter

15Jan2016    500                  2016-01

15Feb2016    250                  2016-01

31Aug2016    1000                 2016-03

30Nov2016    1500                 2016-04

To get the value for quarter, I have extracted the year and quarter values from the Date column, transformed them into text, and then concatenated them using the Merge Columns feature.

When I aggregate by quarter, I have sales of 750 in 2016-01, 1000 in 2016-03, and 1500 in 2016-04. If I graph that, sorted by quarter, it will look like a steadily climbing line from left to right. But note that there are zero sales in Q2. I want my graph to dip from 750 to zero for Q2 and then go back up again for Q3. How do I tell Power BI to display a value for 2016-02 even though there are no data points to aggregate for that time period?

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[David_Schrag]

Yes, that was very helpful and gave me what I needed.

Your solution relies on the modeling function within Power BI Desktop to create the Calendar table from data contained in the Data table. Is modeling like that possible using the Power BI Service? The ultimate goal is to use source data that's contained in OneDrive for Business and present the report on-line, either natively within powerbi.com or embedded in our SharePoint Online environment (in an Office 365 Group site). I can publish the .pbix file to the workspace, so I have a way of sharing the data, but I don't think I can take advantage of the auto-refresh that way. (I'm very new to Power BI, so maybe this is a dumb question.)

[Ashish_Mathur]

You are welcome.  I do not use the PowerBI service very much.  Nevertheless, i do not think you can write any formulas there - you can only create visuals there.

If my reply helped, please mark it as Answer.