Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey all,
Some background:
I am trying to figure out best practice for setting up my budget within my data model.
Currently:
My company uses multiple Consultants for different Projects for a specific Department within the company. Consultants submit budgets for a Project. Projects can be as short as a couple of months, to as long many, many years. Thus, Consultants do not submit budgets annualy like most examples on the forum I've seen. Consultants submit Invoices billing the Company. Thus, my Fact Table = FactInvoiceHeader.
I've seen 3 common scenarios of Budgets within a data model:
1) Create an allocation factor and distribute the Budget within the FactInvoice Table,
2) Create a separate FactBudget Table and using bridge/filter tables build a relationship with the FactInvoiceHeader Table,
3) Create a separate FactBudget Table and use it as a virtual table (#3 is not an option)
Currently I use Scenario #1 (allocate a budget amount from the Project level to the Invoice Level). But I'm asking because Scenario #2 makes a lot of sense to me since you can literally set a budget for the year in a table.I ask because I've seen many examples use Scenario #2 in which they add the measure keys which they want to slice with (Here, 'Consultant Key', 'Project Key', 'Department Key', and then the date component (i.e. 'Invoice Paid Date'(the Date the Invoice was Paid).
The question:
Of scenarios #1 & #2 which is best practice?
Thanks a million!
Hey,
personally I use both pattern, #1 and #2.
At the current moment i tend to use a relationship based approach as a budget sometimes exists on an aggregated level, this is one of use cases for many-to-many relationships.
Maybe this article fill provide additional insights: https://www.daxpatterns.com/budget-patterns/
Regards,
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |