Cumulative values on period

[Anonymous]

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,

Mohammad

[Vvelarde · Accepted solution]

@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 () )

[Anonymous]

@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,

Mohammad PATEL

[Vvelarde]

@Anonymous

The Calendar:

A Calculated Column in Calendar:

Don't related with Data Table:

 The Slicer:

[Anonymous]

@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

[Vvelarde]

@Anonymous

disable the total in the visual table.

[Anonymous]

@Vvelarde

There is no total in the visual.

Mohammad

[Vvelarde]

@Anonymous

What type is your visual...a Table?

[Anonymous]

Indeed, yes it is a table.

Mohammad

[Vvelarde]

@Anonymous

After review your measure, need litle adjustment:

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]))
        )
           * CALCULATE(AVERAGE('Active Policies'[Basic Premium]))
    )
RETURN
if(revenue>0,revenue;BLANK())

[Anonymous]

@Vvelarde

Thanks for your help.

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

Kind regards,

Mohammad

[Vvelarde]

Disable the total from table. I test it in your pbix.

[Anonymous]

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

Mohammad