Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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 () )
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:
A Calculated Column in Calendar:
Don't related with Data Table:
The Slicer:
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
@Anonymous
disable the total in the visual table.
@Anonymous
What type is your visual...a Table?
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())
Thanks for your help.
It is still make me the same issue from the screenshot.
Kind regards,
Mohammad
Thank you for your time and your patience it works now !
Mohammad
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |