Cumulative sales data

[Keith011]

hi there

my goal is to calculate cumulative revenue. I have a sales table (refer below) and a date table(refer below). I have established a relation between those 2 using 'confirmDate' and 'MonthnYear' (many-to-one, filter both way)

How can i achieve my desired table? with a measure.

This is my desired table

confirmDate	Cumulative Revenue
202101	100
202102	600
202103	800
202104	850
202204	1750
202209	1980

Sales Table

confirmDate	Revenue
202101	100
202102	500
202103	200
202104	50
202204	900
202209	230

my fancy Date Table

Date	MonthnYear
Friday, January 1, 2021	202101
Monday, February 1, 2021	202102
Monday, March 1, 2021	202103
Thursday, April 1, 2021	202104
Saturday, May 1, 2021	202105
Tuesday, June 1, 2021	202106
Thursday, July 1, 2021	202107
Sunday, August 1, 2021	202108
Wednesday, September 1, 2021	202109
Friday, October 1, 2021	202110

[v-zhangti · Accepted solution]

Hi, @Keith011 

You don't even need a date sheet. You can try the following methods.

Cumulative Revenue = 
CALCULATE ( SUM ( 'Table'[Revenue] ),
    FILTER ( ALL ( 'Table' ),
        [confirmDate] <= SELECTEDVALUE ( 'Table'[confirmDate] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

your date table is not fancy enough. to use time intelligence functions your calendar table needs to be contiguous and covering.