Adding Rows When there isn't a Ship Date

[Anonymous]

Hello, I have am editing an excel sheet in Power Query that has columns called ship date, item sold, and quantity. I'm trying to find the average of sales for the month but not every day has a shipment. How should I insert a row containing a 0 and the date with no shipments? Thanks.

[Anonymous]

Thank you!

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Don't try to force rows into your fact table.

Create a proper calendar table that you can relate to your fact table then use the calendar[date] column to get the number of days in the period as your denominator.

Example:

You want to look at all shipments in March 2022.

Your calendar table has [date] and [monthYear] columns.

Your fact table has [shipDate] and [shipValue] (sales) columns.

Relate calendar[date] to fact[shipDate], ONE to MANY.

Create the following measures:

_sales = SUM(fact[shipValue])

_days = COUNTROWS(calendar)

_averageSalesPerDay = DIVIDE(_sales, _days, 0)

Put [_averageSalesPerDay] and calendar[monthYear] into a visual and this will calculate the average sales for every day in that month.

Pete