cancel
Showing results for
Did you mean: Helper I

## 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
00000034507          09/10/1972        09/10/1990         10000
00000046073          01/01/1978        01/01/2008         24000
00000047710          01/01/1978        01/01/2004         30000
00000046138          01/01/1979        01/01/1990         21000```

My goal is to get the revenue per year between the start date and the end date, for example if I filter on year 1989 I would have the cumulative total from the begining of each contract to the year choosen.

I have tried many ways like using a calendar table and create a cumulative measure but it failed :

`Calendar = CALENDAR(MIN('Table'[Start]);MAX('Table'[End]))`

```TotalRevenueGenerated =
CALCULATE (
SUM ( 'Table'[ContractRevYear] );
FILTER (
ALL ( 'Calendar'[Date]);
('Calendar'[Date]) <= MAX ( 'Calendar'[Date] )
)
) ```

Could anyone please give me any hint how to achieve this?

Kind regards,

1 ACCEPTED SOLUTION Community Champion

@mpatel

```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 () )``` Lima - Peru
12 REPLIES 12 Community Champion

@mpatel

```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 () )``` Lima - Peru Helper I

@Vvelarde

Hi,

Thank you for your reply. I tried it but i have a blank result.

Is your Calendar table connected to your main table ? How do you manage to create it ?

Kind Regards, Community Champion

@mpatel

The Calendar: A Calculated Column in Calendar: Don't related with Data Table: The Slicer: Lima - Peru Helper I

@Vvelarde

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   Community Champion

@mpatel

disable the total in the visual table.

Lima - Peru Helper I

@Vvelarde

There is no total in the visual. Community Champion

@mpatel

What type is your visual...a Table?

Lima - Peru Helper I

Indeed, yes it is a table. Community Champion

@mpatel

```Premium =

VAR revenue =
IF (
HASONEVALUE( 'Calendar'[Year] ),
IF (
YEAR(VALUES('Active Policies'[Maturity Date])) <= VALUES('Calendar'[Year]),
YEAR(VALUES('Active Policies'[Maturity Date])) - YEAR(VALUES('Active Policies'[Policy Date])),
VALUES ('Calendar'[Year]) - YEAR(VALUES('Active Policies'[Policy Date]))
)
)
RETURN
if(revenue>0,revenue;BLANK())```

Lima - Peru Helper I

@Vvelarde

It is still make me the same issue from the screenshot.

Kind regards, Community Champion
Disable the total from table. I test it in your pbix.

Lima - Peru Helper I

Thank you for your time and your patience it works now !  