Budget table by month in data model

[Anonymous]

Awhile back I build a budget to actual report in PowerBI that has been used throughout the year, But now we are budgeting by month and I need to break it out by month instead of one lump sum budget. 

I tried to create a relationship, many to many, joining the accounts, but that didnt work because there are multiple budgets by account AND by department, then by month.

I have attached some screenshots. Let me know if you have any ideas. I got it to "partially" work by using a lookupvalue and bringing the budget onto the expense table, but I have to use a min or max on the values, so that it doesnt sum all the budgets together. That works, but it doesnt total correct at the bottom or sides. I used the "Dont Summarze" under the Modeling tab, but that still didnt work out.

BudgetExpense

Thanks for the help!

[amitchandak · Accepted solution]

Create a date in the budget table using month and year(was not visible in the screenshot).

Join both expense and budget Across common tables like date, Account, Location, etc. 

If you do not want to join with other tables then use Union a summarize. In this case, also have the date

Test Table= union(SUMMARIZE(Sales,Sales[Brand],"Col1",sum(Sales[Sales]),"Col2",sum(Sales[COGS]),"sort",COUNTROWS(Sales),"Dummy","DDD"),SUMMARIZE(pur,pur[Brand],"Col1",sum(pur[pur]),"Col2",sum(Sales[pur cost]),"sort",COUNTROWS(pur),"Dummy","DDD"))