Hi fellow Power BI lovers,
I’m trying to calculate individual cost items for last year. The year selected is 2019 so it should show 2018 results. I’d like to switch between the cost items hence used SWITCH function - please see the “Test 3” formula below.
The Test 3 formula for your reference is:
Total Overhead LY Test 3 = SWITCH( VALUES( CashFlowTransactions[Account Category 1] ), "Freight Cost", [Expense Freight Cost LY], "IT Related Expenses", [Expense IT Related Expenses LY], "Marketing Expenses", [Expense Marketing Expenses LY], "Other Costs", [Expense Other Costs LY], "Rent", [Expense Rent LY], "Salaries", [Expense Salaries LY], "Staff Development Cost", [Expense Staff Development Cost LY], "Tax Expense", [Expense Tax Expense LY], "Utilities Cost", [Expense Utilities Cost LY] )
My problem is the Test 3 only shows the correct results up until June which is YTD month for this year, while all future months are blank. The individual cost item formula which used a simple DATEADD to shift back 1 year shows correct results for all future months.
Expense Marketing Expenses LY = CALCULATE( [Expense Marketing Expenses], DATEADD( 'Calendar'[Date], -1, YEAR ) )
Could you please advise what would have been wrong with my formula and suggest a possible solution?
Your advice would be much appreciated.