Calculate and filter help!

[Anonymous]

Hi i need help correcting a formula in a measure.

im tryign to calculate the total revenue based upon another table that has a calculated number of months.

i want to get the total revenue in all imnstances where the number of months is 12 (or in my calculation less than 13)

12 Month Revenue = calculate(SUM(H12015[Final Amount (£)]), FILTER(ALL('Start Date'[Length of Tenure Absolute]<13),0))

Can anyone tell me where i am going wrong?

i get the folloiwng error:

A single value for column 'Length of Tenure Absolute' in table 'Start Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[JoHo_BI]

Hi @Anonymous, 

The ALL parameter takes a take to start, so you'll need something like: 

12 Month Revenue = calculate(SUM(H12015[Final Amount (£)]), FILTER(ALL('Start Date'), 'Start Date'[Length of Tenure Absolute]<13),0)

Hope that helps! 

[Anonymous]

thanks unforutnately it didnt work,

 i get the error:  The true/false expression doesnt specify a column.

Also, if i needed to get it to average the final amount would this be right?

So calculate the sum of the amount averaged by the number of people with a tenure of less than 13  months.

12 Month Revenue = calculate(SUM(average(H12015[Final Amount (£)])), FILTER(ALL('Start Date'), 'Start Date'[Length of Tenure Absolute]<13),0)

[JoHo_BI]

Can you post some sample data? It'll be easier to answer if we see how your datais structured.

Thanks!