Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |