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 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.

 

 

Need Help

2 Replies

  • 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?