Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Proud to be a Datanaut!
Thank you!
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.