Power Query Help - Create Daily data from Weekly source data

[Matt888]

Hi,

I'm very new to using Power BI. Hoping to get some advice on the below...

I have 2 sources of data is broadly the same format. I have imported these to Power BI and merge the tables into one (AllWeeklySales). The table has 6 attribute columns and 2 metric columns.

The sales data is only at calendar week level and I have a time dimension table built up from calendar date (Year-Week Number e.g. 202001 is the matching field). Is there a way in Power Query to:-

• Disaggregate the weekly level data daily level including the all sales data table and the date dimension table? (i.e. the metrics will be 1/7 of the weekly value unless the calendar week is a part week over the year end, in which case it will be the weekly value divided by the days in that “week”)
• If this is possible can the resulting daily sales data table include all the fields from the weekly sales table and only calendar date from the date table?
• If the daily table is create using Power Query, once in the model creating visualisations am I able to create new measures using DAX based on the metrics in the daily sales data table. (e.g. I might want to calculate weight based on the number of items and their individual weight defined in a related table)?

Thanks
Matt

 

[GraH]

Stating the obvious maybe, but can't you get your input data fixed, so you have daily sales?
Else I advice to do weekly analysis over daily. As your sales can't be weeksales/number of days in the week. You can still use the calendar table, as the reference date might be any of the dates of that week (most common option would be to use first day of week or last day of week).