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.
Will be interested to hear what you think.
I hope this is helpful.
– Brian