Forum Discussion
Cumulative values on period
Hi everyone, So I am triyng to get the sum of values between two dates column, this is a sample of the data : ContractID Start End ContractRevPerYear
0000...
Anonymous
hi, please try this:
RevenueGenerated = VAR revenue = IF ( HASONEVALUE ( 'Calendar'[Year] ), IF ( YEAR ( VALUES ( Table1[End] ) ) <= VALUES ( 'Calendar'[Year] ), YEAR ( VALUES ( Table1[End] ) ) - YEAR ( VALUES ( Table1[Start] ) ); VALUES ( 'Calendar'[Year] ) - YEAR ( VALUES ( Table1[Start] ) ) ) * CALCULATE ( AVERAGE ( Table1[ContractRevPerYear] ) ) ) RETURN IF ( revenue > 0, revenue, BLANK () )
Thanks.
Unfortunately, i get an error when i choose a year. Well this is the error :
MdxScript(Model) (14, 18) Calculation error in measure 'Calendar'[RevenueGen]: A table with multiple values has been given while unique value is expected
