Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

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

revenue.png




Lima - Peru

View solution in original post

12 REPLIES 12

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.