topic Re: Convert a monthly budget to weekly in DAX Commands and Tips

Convert a monthly budget to weekly

Anonymous — Fri, 13 Mar 2020 13:56:09 GMT

Hi guys,

At our company we have an sales budget per month.

The whole amount is booked at the first day of the month so for example 1-1-2020.

I need to convert this to a weekly budget based on amount of working days.

If i have an week with 5 working days it's no problem for me. I use the following formula: (monthbudget)/(workingdayspermonth)*(workingdaysperweek)

But i don't know how my formula must be if i have an weeknumber that's devided over two monts. Then i have to take also the budget of previous our next month and multiply that by the amount of workingdays that are in the previous or next month.

Can anybody help me?

Re: Convert a monthly budget to weekly

Anonymous — Fri, 13 Mar 2020 14:22:44 GMT

So you say that if you have a week spanning 2 months you want to take one part from one month and the other part from the other?

Best
D

Re: Convert a monthly budget to weekly

Anonymous — Fri, 13 Mar 2020 14:25:39 GMT

Exactly

Re: Convert a monthly budget to weekly

Anonymous — Fri, 13 Mar 2020 15:04:20 GMT

// Calendar (marked as Date table in the model) // must have weeks that are uniquely identified // in the whole calendar. It must also have a column // that says True if a day is a working // day. Name the column [Working Day]. // Name the column with the unique weeks // [WeekId] (this column should normally be hidden // and another should be exposed, like [YearWeek]). // // Say that if you select a unique month ( // [MonthId] should be an int that uniquely determines // the month but you should expose something like '2020-Jan' // instead of an int to the end user), then the measure // [Monthly Budget] gives you the amount for the month. // This one calculates the number of working days // in any period selection from Calendar. [Working Days] = calculate( countrows( 'Calendar' ), keepfilters( 'Calendar'[Working Day] ) ) // This apportions the monthly budget for any // periods of time, not only weeks. If you select // a month, then [Budget] = [Monthly Bugdet]. If // you select any other period of time, then // the period will be decomposed into pieces that // belong to different months and then those will // be summed up. [Budget] = var __datesVisible = values( 'Calendar'[Date] ) var __result = sumx( values( Calendar[MonthId] ), var __monthlyBudget = [Monthly Budget] var __workingDaysPerMonth = [Working Days] var __workingDaysPerMonthInSelection = calculate( [Working Days], keepfilters( __datesVisible ) ) var __budgetInMonthForSelection = divide( __monthlyBudget * __workingDaysPerMonthInSelection, __workingDaysPerMonth ) return __budgetInMonthForSelection ) return __result

Best
D

Re: Convert a monthly budget to weekly

Anonymous — Mon, 16 Mar 2020 14:38:46 GMT

Hmmm, this is not the result i wanted.

This is wat i get, the budget off every month, in the first week of the month.

Re: Convert a monthly budget to weekly

Anonymous — Mon, 16 Mar 2020 23:25:18 GMT

OK... I've created the model and the correct measure (a little tweak was needed to make it right). Please have a look at the file:

https://1drv.ms/u/s!ApyQEauTSLtOgYMvniTALc9BlHQs_w?e=g2FmD4

[Budget] works correctly with any slicing.

Best
D

Re: Convert a monthly budget to weekly

Anonymous — Tue, 17 Mar 2020 06:54:08 GMT

thanks a lot! it works!

Re: Convert a monthly budget to weekly

Anonymous — Tue, 17 Mar 2020 09:41:05 GMT

Kudos would be welcome, if you don't mind, of course.

Thanks.

Best
D

Re: Convert a monthly budget to weekly

dakins — Mon, 01 Aug 2022 22:45:36 GMT

Thank you. I have been looking for a solution for weeks!