I was hoping to get help for spreading an annual (FY June -May) hourly budget spread across the months based on percentages. I have watched the related videos and the video the seems to be the best match is “Seasonal Budgeting Allocations”. To keep this simple I am using an equal percent of .08333 for each of the 12 months.
As you can see from my attached report, the way I am using TREATAS is not providing expected results. I would ultimately like to be able to filter by employee ID as well. I’m not sure why the percentages stop in Jan '21 and do not continue thru May '21. The multiplication aspect of monthly seasonal % X Annual Hourly Budget is not working either.
I have included my calculations(aaMeasure Folder)which try to mirror the Seasonal Budget Allocation article included on this site by Sam.
Before we dive into your specific question, a few notes about your data model:
when using @Melissa’s Extended Date Table, there is no need to snowflake a holiday table off of that. If you reference the date column in your holiday table on the initial invoke screen when the extended date table query fires up, for each date in your date table, it will set the IsHoliday field to true or false based on the holidays contained in your holiday table. (This can also be done after the fact via the advanced editor)
the bidirectional relationship from your date table to your fact table is likely to cause major problems. That should be corrected to a 1:M relationship.
On to the question at hand. Before delving into your DAX, I would recommend perhaps considering an entirely different approach to this problem. I used to solve this the same way, using a complex allocation method linking the budget table to the fact table via TREATAS. However, the Problem of the Week #4 developed by Enterprise DNA Expert @haroonali1000 has led me to completely change my approach when faced with the same problem. I now do the allocation in Power Query. Once this is done, I can then connect the two tables using a traditional physical relationship, and the measures become dramatically simpler.
Here’s the thread for that problem that contains Haroon’s intro and solution videos. I would strongly recommend going through these and considering taking this path instead.
Hi Brian:
Yes definitely helpful. I see how it works on an even spread (total/duration) but I am still wondering how it would apply to a seasonal profile? E.G. if I did not have exactly .0833% for each of the 12 months?
I really like the simplicity of the end result you pointed me too.
Thank you Brian for replying on this. Much appreciated!
I am sure there are number of different ways to do that. The approach that immediately comes to my mind is to build a helper table with your weights by month or quarter that reflect your seasonal patterns. Do the uniform daily allocation per above, then merge the weights from your helper table to your fact table using the month field (or Q if that’s the granularity of your helper table) and then create an adjusted amount multiplying the relevant field (e.g. Sales) by its weight on a row by row basis using a custom column formula or column from examples.
Glad to hear that was helpful. Always a pleasure working with you.
Please let me know how this goes – as I mentioned, I’ve only recently switched over to this approach myself and would be eager to hear others’ experience, especially with allocations more complex than the default uniform approach.