Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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,

 

Mohammad

Need Help
Show and Tell
  • Vvelarde's avatar
    Vvelarde
    9 years ago

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

12 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion
        9 years ago

        Anonymous

         

        The Calendar:

         

         

        A Calculated Column in Calendar:

         

         

        Don't related with Data Table:

         

         

         

         The Slicer: