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
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
Anonymous
Not applicable

@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

 

 

@Anonymous

 

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
Anonymous
Not applicable

@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

 

 

 

 

@Anonymous

 

disable the total in the visual table.

 

 




Lima - Peru
Anonymous
Not applicable

@Vvelarde

 

There is no total in the visual.

 

Mohammad

@Anonymous

 

What type is your visual...a Table?

 




Lima - Peru
Anonymous
Not applicable

Indeed, yes it is a table.

 

Mohammad

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



Lima - Peru
Anonymous
Not applicable

@Vvelarde

 

Thanks for your help.

 

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

 

Kind regards,

 

Mohammad

 

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



Lima - Peru
Anonymous
Not applicable

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

 

Mohammad

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.