Convert monthly budget to weekly

[Anonymous]

Hi,

I am having trouble converting a monthly budget to weekly budget to align with weekly sales.

I have a table with three seperate budgets for three brands I am operating with. The budgets are only available on a monthly basis as the date column in that table is monthly only.

I have another table with all date formats and I managed to create a column showing the number of days per month - the idea is to divide the monthly budget with number of days and then sum it per week.

The two tables are linked at the Date columns.

Can anyone tell me how to calculate and show the weekly budget?

I hope it makes sense.

[bhanu_gautam]

@Anonymous , You can calculate daily budget add aggregate it to weekly using DAX

First create a calculated column for dailybudget

DailyBudget =
DIVIDE (
BudgetTable[MonthlyBudget],
CALCULATE (
COUNTROWS ( DateTable ),
FILTER (
DateTable,
DateTable[Year] = BudgetTable[Year] &&
DateTable[Month] = BudgetTable[Month]
)
)
)

Now create a measure to sum it

DAX
WeeklyBudget =
SUMX (
DateTable,
CALCULATE (
SUM ( BudgetTable[DailyBudget] ),
FILTER (
BudgetTable,
BudgetTable[Year] = DateTable[Year] &&
BudgetTable[Month] = DateTable[Month]
)
)
)

[Anonymous]

Hi @bhanu_gautam,

Appreciate your answer! I followed your instructions, and the sums for daily and weekly budget seems correct - however, it is still only the first date of each month that seem to have the value shown:

I was hoping to see the budget for each week during the year. Any suggestions?