EazyBI calculated member problem

Question

Hi, I´m triying to define a calculated member that when a date measure is greater than current day, it shows the current day: CASE WHEN DateCompare([Measures].[Issue Target end],Now())=-1 THEN [Measures].[Issue Target end] ELSE NOW() END But it gives me this error: Formula is not valid: No function matches signature 'CASE WHEN THEN ... END' How can i get this?

Matt C_ Wilson · Answer

In my case (no pun intended) I was evaluating a set of calculated members that were booleans.  My first attempt looked like this:

CASE
  WHEN [Members].[Custom Boolean Field 1] THEN 'Case 1'
  WHEN [Members].[Custom Boolean Field 2] THEN 'Case 2'
  ELSE 'Other'
END

And I was getting the same formula error.  I needed to adjust it to:

CASE
WHEN [Members].[Custom Boolean Field 1].Value THEN 'Case 1'
WHEN [Members].[Custom Boolean Field 2].Value THEN 'Case 2'
ELSE 'Other'
END

Martins Vanags_eazyBI · Answer

Hi,

You are on the righ track, however, the "Case when" structure is picky for formats.

Try using DateParse to force the right format for the Issue target end property.

CASE WHEN DateCompare([Measures].[Issue Target end],Now())=-1
THEN
DateParse([Measures].[Issue Target end])
ELSE NOW()
END

And don't forget to save your calculated measure in the right format (Month Day Year)

Martins / eazyBI support