Calculate Daily Budgets based on Previous Year sales and this year monthly budget values

[reddy421]

I have a PBI solution with the sales for the last x number of year for each facility and the summary table looks like below.

 

I have the Monthly Budgets for this year as below .

There is a relation between my Summary table to date, facility.
There is a relation between my Monthly Budgets to the facility.

I need to calculate the daily budgets for each store based on last years sale(Sample pic shown below for reference).

FYI.. We are not doing date to date but day to day(The highlited boxes in the below pic)

 

Any help is really appreciated.

[adudani]

hi @reddy421 ,

consider:

1. Converting you monthly budget into daily budgets. ( Refer to List.Dates function for this conversion : this given a column with a list of all dates between the start date and end date of the month. Another column, dividing the metric= ex. sales by the number of days in the period ( end date - start date +1) . Then expand the dates.)

2. Merge the daily budget on the daily sales table using a key ( join on : Date+FacilityID)

3. This should enable using any time intelligence calculations.

if this doesn't resolve the issue, kindly provide sample data masking sensitive information in a usable format (excel, csv, table etc.)