Forum Discussion

Keith011's avatar
Keith011
Helper III
3 years ago
Solved

Cumulative sales data

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

confirmDateCumulative Revenue
202101100
202102600
202103800
202104850
2022041750
2022091980

 


Sales Table

confirmDateRevenue
202101100
202102500
202103200
20210450
202204900
202209230

 

my fancy Date Table

DateMonthnYear
Friday, January 1, 2021202101
Monday, February 1, 2021202102
Monday, March 1, 2021202103
Thursday, April 1, 2021202104
Saturday, May 1, 2021202105
Tuesday, June 1, 2021202106
Thursday, July 1, 2021202107
Sunday, August 1, 2021202108
Wednesday, September 1, 2021202109
Friday, October 1, 2021202110
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

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

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    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.