cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
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,

 

Mohammad

1 ACCEPTED SOLUTION
Community Champion
Community Champion

@mpatel

 

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

@mpatel

 

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

@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

 

 

Community Champion
Community Champion

@mpatel

 

The Calendar:

 

CalendarAuto.png

 

A Calculated Column in Calendar:

 

Year.png

 

Don't related with Data Table:

 

 

tables.png

 

 The Slicer:

 

Slicer.png

 

 




Lima - Peru

@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

Capture d’écran 2017-04-08 à 07.57.48.png

 

 

Capture d’écran 2017-04-08 à 08.01.01.png

 

 

 

 

Community Champion
Community Champion

@mpatel

 

disable the total in the visual table.

 

 




Lima - Peru

@Vvelarde

 

There is no total in the visual.

 

Mohammad

Community Champion
Community Champion

@mpatel

 

What type is your visual...a Table?

 




Lima - Peru

Indeed, yes it is a table.

 

Mohammad

Community Champion
Community Champion

@mpatel

 

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



Lima - Peru

@Vvelarde

 

Thanks for your help.

 

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

 

Kind regards,

 

Mohammad

 

Community Champion
Community Champion

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



Lima - Peru

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

 

Mohammad

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors