Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 () )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |