Cumulative Sales by Agent for date range

[Kris55]

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

Cumulative Sales = CALCULATE(

    SUM(opportunityproducts[Value (GBP)]),

    Filter(

        'opportunityproducts',

        'opportunityproducts'[Actual_Close_Date] >= MIN('Date'[Date]) &&

        'opportunityproducts'[Actual_Close_Date] <= MAX('Date'[Date]) &&

        'opportunityproducts'[Agent] = SELECTEDVALUE('opportunityproducts'[Agent])

    )

)

[FreemanZ · Accepted 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:

[Kris55]

Thank you so much FreemanZ, 

This has worked perfectly.