Hi, I am super excited to be part of this community. Thanks for your hard work, Sam.
Anyhow, I am having trouble allocating a monthly budget to the date level. I have reviewed (many times) the tutorials, however, I remain at the same stumbling block. Ultimately, I think it’s an issue of granularity.
The budget I am referring to is at the year/month level but it’s also broken-out by Location, Customer , Product.
The issue I am having is that the Total Budget Amount only exists on the first day of the month and it’s the sum of the entire month. I would like this to allocate over the days.
Hi Again.
I wanted to also share how the date is related to the budget. FirstDay of the Month -> Dim_Date[Full Date]
In the picture, you will also see the granularity in the highlighted Table.
Thanks
Not sure if this would help you here but I had sort of a similar requirements that we get our targets per salesperson per month, and I needed it on a daily granularity so I could make a nice graph out of it.
What I did was “generating” the daily value for it using a crossjoin with the datetable
DailyTargets = CROSSJOIN(FILTER(zz_Dim_Date, zz_Dim_Date[Is_Business_Day] = true), Values(SalesTargets[SalesPerson]))
Not sure if that helps you any further but what It gave me was a DailyTargets table which had a target for each day of the months the sales persons had a target.
Its always a bit hard to find out if it will help you but thought I will share how I got something similar going.
1 Like
Certainly check out this resource if you haven’t already to see how I set it up
Also here’s the workshop when you have the time to review.
The reason I’m adding here is the model setup is relatively similar to what you have.
You’ll see that I don’t actually have any relationship to the date table, I think you better the do this also.
I allocate the budget with the formula instead.
Budget Allocation =
VAR DaysinContext = COUNTROWS( Dates )
VAR DaysinMonth = CALCULATE( COUNTROWS( Dates ), ALL( Dates ), VALUES( Dates[Month & Year] ) )
VAR CurrentMonth = SELECTEDVALUE( Dates[MonthName] )
VAR MonthlyBudgetAmounts = CALCULATE( [Total Budgets], FILTER( ALL( 'Product Budgets'[MonthName] ) , CurrentMonth = 'Product Budgets'[MonthName] ) )
RETURN
IF( OR( HASONEVALUE( Dates[Date] ), HASONEVALUE( Dates[Month & Year] ) ),
DIVIDE( DaysinContext, DaysinMonth, 0 ) * MonthlyBudgetAmounts,
[Total Budgets] )
What this is essentially doing is working out the allocation. What you’ll need I think is something like:
1 / 30 (or 1 / 31) because your data is at the month & year
You see the above formula actually does that. So you would just need similar logic.
You see also the CurrentMonth variable. For you this will likely be Month&Year instead.
See if this helps. I might come back to this
1 Like
I definitely feel the relationship is putting you off here. There really shouldn’t be a physical relationship because it’s not a perfect one.
It’s definitely better to do this with the formula.
What I would recommend it to break out each VAR I’ve used in the master formula into separate one. Then you can check they are all calculating correctly.
The MonthlyBudgetAmounts part of the formula is like a fancy vlookup.
Your calculating the total budget amount and with filter your looking up the current Month&Year from the date context to the budget table.
Hopefully this all makes sense.
Let me know how you go.
1 Like
Thanks for taking time to help me out. I will let you know if I implement your code.
Sam, thanks so much for your thorough response and help. I think the newest video you Posted on YouTube today will be the answer I’m looking for. I will follow up and let you know. Thanks again
Yes using TREATAS is another good option. Lucky on the timing there.
Hi Sam, the treat as works great, thanks. So far, I have it at Monthly, Customer and Product granularity. In the video, you mention continuing the TREATAS function to allocate to a daily level. I cannot figure that out. The daily level doesn’t exist. Do you have any additional advice, or, should I use a technique from the advanced budgeting series? Thanks again!
(I added a screen capture so you can see where I am. You’ll notice that the days are not contiguous.
Not sure if that matters?)
This is the key part from here
You need to create the allocation formula using the above logic.
Then go
( DaysinContext / DaysinMonth ) * …your current result you already have…
1 Like