5 Replies Latest reply: Oct 6, 2015 11:43 PM by Guohao Yee

How to include an formula in an expression?

Hi All,

I am trying to obtain the value for the first month of the year. (E.g. 1/1/2015.) in order to compute YTD % Change.

Sum({\$<Date={'=\$(YearStart(Max(Date)))'}>}[Sales])

Ideally, it should be evaluated as

Sum({\$<Date={'1/1/2015'}>}[Sales]).

• Re: How to include an formula in an expression?

Close:

Sum( {<Date={'\$(=Date(YearStart(Max(Date))))'}>} Sales)

Also , i suggest ensuring that 'Date' is a properly formatted Date in the load editor so that it will form the right 'equivalence' operation.

Date(Date) as Date,

etc..

• Re: How to include an formula in an expression?

Note that Max(Date) will be the most recent date in the current selection state.

You could also use max( {1} Date) to be the most recent date in your whole data set regardless of selection state

and commonly used is just today's date using the   Today() function

Sum( {<Date={'\$(=Date(YearStart(Max({1} Date))))'}>} Sales)

Sum( {<Date={'\$(=Date(YearStart(Today()))'}>} Sales)

• Re: How to include an formula in an expression?

The Date(Date) Function is definitely a useful tip!

Thanks John!

• Re: How to include an formula in an expression?

Try this may be:

=Sum({\$<Date={"\$(=Date(YearStart(Max(Date)), 'M/D/YYYY'))"}>}Sales)

Change your date format if it is D/M/YYYY

HTH

Best,

Sunny

• Re: How to include an formula in an expression?

Yeah, it was the date format that wasn't captured correctly.

Thanks a mil Sunny!