YTD Calc is Erroring on Me

[cpadilla0024]

I am having issues with an interesting calculation. The fun part is that our reporting for current month has iterations (Day 4, Day 5, etc) before becoming "Final." So, I need to display a YTD calc for the ["Final" for the previous months + the Selected Iteration for the current month]. Below is my formula with variables. I get an error when I attempt to add two CALCULATE functions together at the end of my SWITCH. Please help.


z_YTD Actual =
VAR SelectedMonth = SELECTEDVALUE('Trend'[Month])
VAR SelectedYear = SELECTEDVALUE('Trend'[Fiscal Year])
VAR FirstDay = DATE(SelectedYear,1,1)
VAR LastDay = LOOKUPVALUE('Calendar'[Date],'Calendar'[Fiscal Year],SelectedYear,'Calendar'[Month], SelectedMonth)
VAR SelectedPeriod = LOOKUPVALUE('Calendar'[Period],'Calendar'[Month],SelectedMonth)
VAR SelectedLineDesc = SELECTEDVALUE('PL Format'[Line Description])
VAR SelectedVarClass = SELECTEDVALUE('Trend'[Variance Class])
VAR LastDay1 = LOOKUPVALUE('Calendar'[Date],'Calendar'[Fiscal Year],SelectedYear,'Calendar'[Month],FORMAT(EOMONTH(LastDay,-1), "mmm"))
VAR SelectedIteration = SELECTEDVALUE('Trend'[Iteration])


RETURN
SWITCH(TRUE(),
SelectedIteration = "Final",
CALCULATE(
[MTD Actual],
DATESBETWEEN('Calendar'[Date], FirstDay, LastDay),'Trend'[Fiscal Year] = SelectedYear),
CALCULATE(
[MTD Actual],
DATESBETWEEN('Calendar'[Date], FirstDay, LastDay1),'Trend'[Fiscal Year] = SelectedYear,'Trend'[Iteration] = "Final"))

 

[cpadilla0024]

Follow-up: I keep getting this and I cannot figure out why: Cannot convert value " of Type Text to type Numeric/Date

 

[GraH - Guido]

The error message is quite obvious, you have an incompatibility in data types somewhere. Now, that does not really help you, I know.
Looking at your DAX, I'm wondering about two things:

1. if your LastDay/LastDay1 LOOKUPVALUE should not be replaced with RELATED function?
2. if DATESBETWEEN can be replaced by a filter on the date table?

Now, there are other more DAX proficient members active here. Hopefully, they can spot the error precisely.

 

[Chihiro]

Don't think we could really help without knowing type of data held in your column(s) and table(s).

One thing you could do, extract out each of VAR into another measure and return the value. Check data type of each. That could potentially identify data type mismatch.

Note: SelectedVarClass, SelectedLineDesc, SelectedPeriod are not used after it's defined. I'd recommend removing it from DAX.