Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Everyone,
I have looked everywhere, google ChatGPT but can't get anything to work.
I need a DAX command to show the cumulative sales by agent by date for Q3-Q4 2023.
Date Agent Value Cumulative
01/06/2023 A 10 10
02/06/2023 A 100 110
03/06/2023 A 325 435
The problem is all dax commands I have seen up to now don't actually do what they say they will and look like this
Date Agent Value Cumulative
01/06/2023 A 10 10
02/06/2023 A 100 100
03/06/2023 A 325 325
This is what ChatGPT said but it feels like this DAX will only show sum for the date and not add up previous dates
Solved! Go to Solution.
hi @Kris55
try to add a calculated column like:
Column =
SUMX(
FILTER(
data,
data[Agent]=EARLIER(data[Agent])
&&data[Date]<=EARLIER(data[Date])
),
data[Value]
)
or plot a table visual with columns and a measure like:
measure =
SUMX(
FILTER(
ALLSELECTED(data),
data[Agent]=MAX(data[Agent])
&&data[Date]<=MAX(data[Date])
),
data[Value]
)
it worked like:
hi @Kris55
try to add a calculated column like:
Column =
SUMX(
FILTER(
data,
data[Agent]=EARLIER(data[Agent])
&&data[Date]<=EARLIER(data[Date])
),
data[Value]
)
or plot a table visual with columns and a measure like:
measure =
SUMX(
FILTER(
ALLSELECTED(data),
data[Agent]=MAX(data[Agent])
&&data[Date]<=MAX(data[Date])
),
data[Value]
)
it worked like:
Thank you so much FreemanZ,
This has worked perfectly.
User | Count |
---|---|
58 | |
21 | |
18 | |
16 | |
13 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |