Forum Discussion
Cummulative sum for each driver
- Anonymous8 years ago
I have reproduced your required table.
To do so I have the following measure
Cumulative Quantity = IF ( MIN ( 'DateTable'[DateID] ) <= CALCULATE ( MAX ( Table1[DateID] ), ALL ( Table1 ) ), CALCULATE ( SUM ( Table1[DriverPoints] ), FILTER ( ALL ( 'DateTable'[Date] ), 'DateTable'[Date] <= MAX ( 'DateTable'[Date] ) ) ) )This measure is esentially the same with just an extra bit to remove numbers in the future of your fact table. The important fix is to use the date in the table rather than the dateID
In terms of tables I have your fact table (Table1) linked to a date table that contains the date as well as the date ID with a one to many relationship between DateTable[DateID] and Table1[DateID].
- 8 years ago
Done! Thanks guys!
CumulativePointsByYearDrivers =
IF (
MIN ( 'datum'[dateId] ) <= CALCULATE ( MAX ( rezultat[dateId] ); ALL (rezultat) );
CALCULATE (
SUM ( rezultat[pointsDriver] );
FILTER (
ALL ( 'datum'[dateOfRace].[Date] );
'datum'[dateOfRace].[Date] <= MAX ( 'datum'[dateOfRace].[Date] ) && year( datum[dateOfRace].[Date]) = year(MAX(datum[dateOfRace].[Date]))
)
)
)
I have reproduced your required table.
To do so I have the following measure
Cumulative Quantity =
IF (
MIN ( 'DateTable'[DateID] )
<= CALCULATE ( MAX ( Table1[DateID] ), ALL ( Table1 ) ),
CALCULATE (
SUM ( Table1[DriverPoints] ),
FILTER (
ALL ( 'DateTable'[Date] ),
'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
)
)
)This measure is esentially the same with just an extra bit to remove numbers in the future of your fact table. The important fix is to use the date in the table rather than the dateID
In terms of tables I have your fact table (Table1) linked to a date table that contains the date as well as the date ID with a one to many relationship between DateTable[DateID] and Table1[DateID].
Thank you very much! Addition what I had to make was:
'DateTable'[Date].[Date]
Now it works pefect!
Now things started to get interesting but I have one more question. How to reset cumulative after each year? It would be perfect for drilldown graph.
- dinek8 years agoFrequent Visitor
Done! Thanks guys!
CumulativePointsByYearDrivers =
IF (
MIN ( 'datum'[dateId] ) <= CALCULATE ( MAX ( rezultat[dateId] ); ALL (rezultat) );
CALCULATE (
SUM ( rezultat[pointsDriver] );
FILTER (
ALL ( 'datum'[dateOfRace].[Date] );
'datum'[dateOfRace].[Date] <= MAX ( 'datum'[dateOfRace].[Date] ) && year( datum[dateOfRace].[Date]) = year(MAX(datum[dateOfRace].[Date]))
)
)
)