Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
your date table is not fancy enough. to use time intelligence functions your calendar table needs to be contiguous and covering.
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |