Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Convert a monthly budget to weekly

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? 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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

Anonymous
Not applicable

Hmmm, this is not the result i wanted. 

return.PNG

 

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

Anonymous
Not applicable

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

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

Anonymous
Not applicable

thanks a lot! it works! 

Anonymous
Not applicable

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

Thanks.

Best
D
Anonymous
Not applicable

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
Anonymous
Not applicable

Exactly

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors