Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Convert monthly budget to weekly

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 a...
Need Help
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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?