Adding Rows When there isn't a Ship Date

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