I have a budget file which contains monthly information and a dates table.
I want to create another table which contains the daily budget information. I have used the following formula to generate that table:
CALCULATETABLE(
ADDCOLUMNS(
CROSSJOIN(
SUMMARIZE(
'Budget New',
'Budget New'[gle_departmentHelper],
'Budget New'[calcDepartment Helper],
'Budget New'[Category Helper],
'Budget New'[Item Category],
'Budget New'[Item Type]
),
SELECTCOLUMNS(FILTER(Dates, Dates[Year]=2023),Dates[Date], Dates[Year-Month])
),
"@Gross Month budget",CALCULATE([Budget Gross Order], REMOVEFILTERS(Dates[Date])),
"@Net Month budget",CALCULATE([Budget Net Orders], REMOVEFILTERS(Dates[Date])),
"@Days in Month", CALCULATE(COUNTROWS( Dates ), REMOVEFILTERS(Dates[Date]), Dates[Day of Week]<>1),
"@Gross Daily Budget", DIVIDE(cALCULATE([Budget Gross Order], REMOVEFILTERS(Dates[Date])),CALCULATE(COUNTROWS( Dates ), REMOVEFILTERS(Dates[Date]), Dates[Day of Week]<>1),0),
"@Net Daily Budget", DIVIDE(cALCULATE([Budget Net Orders], REMOVEFILTERS(Dates[Date])),CALCULATE(COUNTROWS( Dates ), REMOVEFILTERS(Dates[Date]), Dates[Day of Week]<>1),0)
),
FILTER(Dates, Dates[Year]=2023)
)
The summarize function gives me all the column combinations from the budget file with the selectcolumns then adding dates daily to the table. I can then calculate the daily budget by finding the total budget for the month and dividing it by the number of workdays in the month.
The code above creates the table exactly as I want but I then need to create relationships from the newly created budget table to my other tables. When I try and create a relationship between my date in the newly created budget table to the dates table, I get a circular dependency error.
I have read that this can be solved by using Allexcept but I am not sure where I should be using it.
