@Whitewater100,
Great - really glad to hear that was helpful.
Just a couple of preliminary notes first:
- Don’t worry about sending files through Gmail or other direct means. Per the rules of the forum established by @sam.mckay, all business has to be conducted via the forum. In addition to answering individual’s questions, the goal of the forum is to serve as a knowledge base for all members.
- Be sure to mark your date table as a date table, and validate on the date field. This is an important safety check to ensure your time intelligence functions will work correctly.
- Be sure to turn off the “automatically detect relationships” options. I noticed your current file had a bunch of nonsense relationships connected to the tables we created yesterday, and I’m certain that the autodetect is the culprit. If Power BI is a masterpiece (and I think it is), autodetect relationships is the mustache and Groucho glasses scrawled on the painting by vandals when the security guard was asleep. I removed all these relationships, since our monthly budget forecast table is a disconnected supporting table that we will link via virtual relationships using TREATAS.
OK - on to the daily allocations. You are very much on the right track here, but veered off course in a few important but easily remedied ways.
- If you notice, your Total Budget Qty never varies, despite looking like it’s displayed in context individual by date. The reason is, as mentioned above, that there is no physical relationship between this forecast table and the date table. If there was, your measure would work perfectly:
Total Budget Qty = SUM(‘Monthly FCast by Brand’[Brand Qty FC])
However, we now have to create the relationship virtually via TREATAS within the measure:
Total Monthly FCast Qty TREATAS =
CALCULATE(
SUM('Monthly FCast by Brand'[Brand Qty FC]),
TREATAS(
VALUES( '20Dates'[MonthName] ),
'Monthly FCast by Brand'[Month]
)
)
And Boom! With three short additional lines of DAX, we’ve now both created the virtual relationship between 20Dates and Monthly FCast by Brand, AND solved our many-to-many relationship problem on month name. Our forecast budgets are now varying properly by month:
- In terms of the daily allocation, you correctly tried to make the virtual connection using TREATAS, but connected the wrong element:
TREATAS(VALUES(‘20Dates’[MonthnYear]), ‘20Dates’[MonthnYear]))
What this basically did was connected the unique MonthnYear values from the 20Dates dimension table back to itself. While this surprisingly didn’t throw an error message, it also didn’t really do anything. Instead the unique MonthnYear values from the 20Dates dimension table need to be connected to the appropriate matching column in your fact table:
TREATAS(
VALUES( ‘20Dates’[MonthName] ),
‘Monthly FCast by Brand’[Month]
)
With this change, as well as changing the reference to the measure in 1. above, the revised daily allocation formula now runs like a champ:
Total FCast Qty Daily TREATAS =
VAR DaysinContext =
COUNTROWS ( '20Dates' )
VAR DaysinMonth = [Days in Month]
VAR CurrentMonth =
SELECTEDVALUE ( '20Dates'[MonthName] )
VAR CurrentBBrand =
SELECTEDVALUE ( 'Monthly FCast by Brand'[Brand] )
VAR MonthlyBrandBudgets = [Total Monthly FCast Qty TREATAS]
VAR DailyAlloc =
IF (
OR (
HASONEVALUE ( '20Dates'[Date] ),
HASONEVALUE ( Dates[MonthnYear] )
),
DIVIDE (
DaysinContext,
DaysinMonth,
0
) * MonthlyBrandBudgets,
[Total Monthly FCast Qty TREATAS]
)
RETURN
DailyAlloc
Now, it’s a pretty simple matter to get our Actuals - Forecast by just subtracting the two measures. The only complexity here is adding an ISINSCOPE test to determine whether to return the daily or monthly value, depending on the row header of the matrix visual:
Sales Q Minus Forecast =
IF(
ISINSCOPE( '20Dates'[Date] ),
[Total Q Sold] - [Total FCast Qty Daily TREATAS],
[Total Q Sold] - [Total Monthly FCast Qty TREATAS]
)
Here’s what it all looks like when put together:
Honestly, I steered away from TREATAS for a long time, because I didn’t fully understand what it was doing or how to use it, and the Microsoft explanation is complete gibberish. However, Sam’s video that I posted above does a fantastic job explaining it, although you may have to watch it two or three times (I did…) to pick up all the nuance. However, it’s definitely worth the effort since you can do some nearly magical things with it once you understand how it works.
Long post, but I hope this is all helpful. Full solution file posted below.
