Hi, im using the Switch True measure Sam presented in the financial reporting session. I want to be able to show MTH, QTD & YTD in the same table, I created all the measures but I think that the FILTER in the SWITCH statement limits the results, im only getting the selected MTH for all other Current Items not explicitly referenced.
The other four measures in the SWITCH are working fine hence my assumption its the filter.
Im using a slicer to limit the MTH in the report, the slicer is only showing month name but its the first date of the month. Im using a relative date filter to limit it to the last 12 months.
YTD Actuals =
VAR CurrentItem = SELECTEDVALUE( ‘Report Template’[Report Items (Normalised)] )
RETURN
SWITCH( TRUE(),
CurrentItem = “TOTAL TURNOVER”, [Revenues YTD] ,
CurrentItem = “TOTAL ADMINISTRATIVE COSTS”, [Costs YTD] ,
CurrentItem = “PRE MANCO EBITDA”, [EBITDA YTD] ,
CurrentItem = “PRE MANCO EBITDA %”, FORMAT([EBITDA % YTD], “0.00%”) ,
CALCULATE( [Actuals YTD], FILTER(IncomeStatementData, IncomeStatementData[Description] = CurrentItem )))
Revenues YTD =
CALCULATE([Income Values], IncomeStatementData[Category] = “Revenue”, IncomeStatementData[Type] = “Actual”, DATESYTD(‘Calendar’[Date]))
Actuals YTD =
VAR Revenue = CALCULATE( [Income Values], IncomeStatementData[Category] = “Revenue” , IncomeStatementData[Type] = “Actual”, DATESYTD(‘Calendar’[Date]))
VAR Expense = CALCULATE( [Income Values], IncomeStatementData[Category] = “Cost” , IncomeStatementData[Type] = “Actual”, DATESYTD(‘Calendar’[Date])) * -1
RETURN
IF( SELECTEDVALUE (IncomeStatementData[Category] )= “Revenue”, Revenue,
IF ( SELECTEDVALUE(IncomeStatementData[Category]) = “Cost”, Expense,
Revenue + Expense ))
Any idea how I can fix this?