Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi - my table has non-unique dates and I'm trying to calculate a running total for a given date AND text filter pair. My data looks the equivalent of something like the below and what I'm aiming for is something like:
RunningTotal = calculate(sum('Table'[Revenue]) where WeekEndingDate <= Earlier(WeekEndingDate) AND Store = Store
Cumulative Revenue should calculate the running total of revenue for the row 'Store' up to the row 'WeekEndingDate'
Maybe I need ALLEXCEPT or ALLSELECTED but I can't quite get it to work. I've tried as many relative examples as I could find.
WeekEndingDate | Store | Revenue | Cumulative Revenue |
7-Mar-21 | Strawberry Lane | $7.74 | |
14-Mar-21 | Strawberry Lane | $7.71 | |
21-Mar-21 | Strawberry Lane | $8.27 | |
28-Mar-21 | Strawberry Lane | $1.65 | |
4-Apr-21 | Strawberry Lane | $3.15 | |
11-Apr-21 | Strawberry Lane | $0.67 | |
18-Apr-21 | Strawberry Lane | $6.68 | |
25-Apr-21 | Strawberry Lane | $7.03 | |
2-May-21 | Strawberry Lane | $2.88 | |
9-May-21 | Strawberry Lane | $2.10 | |
16-May-21 | Strawberry Lane | $3.46 | |
23-May-21 | Strawberry Lane | $6.15 | |
30-May-21 | Strawberry Lane | $1.28 | |
6-Jun-21 | Strawberry Lane | $1.78 | |
13-Jun-21 | Strawberry Lane | $7.74 | |
11-Apr-21 | High St | $0.14 | |
18-Apr-21 | High St | $8.35 | |
25-Apr-21 | High St | $3.09 | |
2-May-21 | High St | $7.47 | |
9-May-21 | High St | $3.30 | |
16-May-21 | High St | $1.69 | |
23-May-21 | High St | $7.32 | |
30-May-21 | High St | $8.19 | |
6-Jun-21 | High St | $0.20 | |
13-Jun-21 | High St | $0.20 | |
28-Mar-21 | Mountain View | $5.70 | |
4-Apr-21 | Mountain View | $8.82 | |
11-Apr-21 | Mountain View | $9.32 | |
18-Apr-21 | Mountain View | $3.61 | |
25-Apr-21 | Mountain View | $3.80 | |
2-May-21 | Mountain View | $3.78 | |
9-May-21 | Mountain View | $5.51 | |
16-May-21 | Mountain View | $6.56 | |
23-May-21 | Mountain View | $8.85 | |
30-May-21 | Mountain View | $6.20 | |
6-Jun-21 | Mountain View | $6.35 | |
13-Jun-21 | Mountain View | $7.87 |
Solved! Go to Solution.
Hi @rack201
Try this code to add a column to your table:
Cumulative Revenue =
VAR _Date =
FIRSTNONBLANK ( 'Table'[WeekEndingDate], "" )
RETURN
CALCULATE (
SUM ( 'Table'[Revenue] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Store] ),
'Table'[WeekEndingDate] <= _Date
)
)
Output :
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos ✌️!!
Hello guys 😀
Code works perfektly thanks 💪
But any idea how to manipulate the code to calculate the average instead of sum?
Thanks for your contribution
Cheers
qwertzuiop
Hi @rack201
Try this code to add a column to your table:
Cumulative Revenue =
VAR _Date =
FIRSTNONBLANK ( 'Table'[WeekEndingDate], "" )
RETURN
CALCULATE (
SUM ( 'Table'[Revenue] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Store] ),
'Table'[WeekEndingDate] <= _Date
)
)
Output :
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos ✌️!!
Brilliant - Thank you VahidDM..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |