custom table with columns from 2 tables having M-to-M relationship

[Anonymous]

Hi,

I am having a date table with columns like Date,YrMonth,Year columns.There is another table with 2 columns Year,BudgetCost.

I want to obtain a custom table that gets me per month cost$ .Per month $ is obtained by dividing Cost$/12.Attached is the Date and Budget table along with expected output.

Between Budget and Date is a M-to-M relation.

[lbendlin]

I see no reason for a M:M relationship.  1:M from Dates to budget is enough. But your budget table is missing a "peg" column.  Instead of Year=2020 it should have Year = "1/1/2020" so you can tie it into the dates table.

[Anonymous]

Ok ,if i have a column 1/1/2020 in the budget table,how do i arrive at the expected result?I thought about this but dropped the idea because adding 1/1/2020 will get me the expected result for just 2020-01 not the other months.Can you describe.

[lbendlin]

Apologies, should have read your issue description better.

If all you do is divide the budget by 12, what do you need any of the tables for?  Are you planning to have a table with 12 months showing all the same monthly cost? That would be slightly redundant.

Or do you have different boundaries (like some of the budgets only last for part of the year)?

[Anonymous]

The requirement is below:

• Users want to calculate the budgeted at different levels example- per department per product per month .This is the lowest grain.Department comes from another table .
• The Budget table has year as the granularity.I felt i would have to have the budget $ at the montth grain so as to show cost at the lowest grain and then roll up to year.

To answer your questions-

The budgeted cost per month does not change and is linear division by 12 each year.

[lbendlin]

I didn't see department in your sample data - how does that tie in? Is it calculated based on department size?

Based on your description I would create a budget table in Power Query that divides the value by 12 and then pegs it to the first of each month.  So instead of having a table with three rows (year, product, full cost)  you would have a table with 36 rows (first day of month, product, cost/12).  That monthly budget table can then be properly linked to the Dates table.

[Anonymous]

The department is from employee table and goes by headcount.

As you say,divide cost by 12 and peg it to Budget table is what my expected result in the image i attached.I am trying to get there with DAX and not sure how to.Also,The budget table is not a one time load.The budget forecast will done for 2021-2025 and the budget table will contain same detail product,budget year,cost for the next 5 yrs.

[lbendlin]

As I said, I would do that in Power Query (M), not in Power BI (DAX).

[Anonymous]

Thanks for your replies.Why do you recommend power query ?

[lbendlin]

It's a one time calculation, and these should be done as far upstream as possible.

Here's an example.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUTI0MjUwADF8g/PT0jKTU5VidRCSZgYGENngxJzU4rT8IlR5U7h0alEZUK9ffrlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BudgetYear = _t, #"Cost$" = _t, Product = _t]),
    ML = List.Generate(()=>1,each _ < 13, each _ + 1),
    #"Converted to Table" = Table.FromList(ML, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Months = Table.AddColumn(Source,"Month",each #"Converted to Table"),
    #"Expanded Month" = Table.ExpandTableColumn(Months, "Month", {"Column1"}, {"Column1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Month",{{"BudgetYear", Int64.Type}, {"Column1", Int64.Type}, {"Cost$", Currency.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each Date.From(#date([BudgetYear],[Column1],1))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Monthly Cost", each [#"Cost$"]/12),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Product", "Date", "Monthly Cost"})
in
    #"Removed Other Columns"

[Anonymous]

Thanks much,i will try this