Cumulative Sum Chart

[bluestonewales]

HI,

I am struggling to get a measure to show the cumulative impact of sales for a particular product (which has a valid date) and an insert date (which is when the booking was created)

I am using the formula below

Cummulative Actual Revenue =
CALCULATE (
sum(DetailedBookingsTable[RV]),filter(DetailedBookingsTable,DetailedBookingsTable[Arrival Date].[Date]),
FILTER (
ALL ( DetailedBookingsTable),
DetailedBookingsTable[Insert Date].[Date] <= MAX(DetailedBookingsTable[Insert Date].[Date] )))  

and the chart looks like below

It is accurately showing the daily sales for the product but not cumulatively adding them up.

Once I have got this working then I have to try to get the same data for prior year on the same graph,.

Any help would be appreciated.

[Eric_Zhang]

---

@bluestonewales wrote:

HI,

I am struggling to get a measure to show the cumulative impact of sales for a particular product (which has a valid date) and an insert date (which is when the booking was created)

I am using the formula below

Cummulative Actual Revenue =
CALCULATE (
sum(DetailedBookingsTable[RV]),filter(DetailedBookingsTable,DetailedBookingsTable[Arrival Date].[Date]),
FILTER (
ALL ( DetailedBookingsTable),
DetailedBookingsTable[Insert Date].[Date] <= MAX(DetailedBookingsTable[Insert Date].[Date] )))  

and the chart looks like below

It is accurately showing the daily sales for the product but not cumulatively adding them up.

Once I have got this working then I have to try to get the same data for prior year on the same graph,.

Any help would be appreciated.

---

@bluestonewales

I see two filters in your case, usually they shall all apply ALL(table) in this kind of case. And there're two dates in the DAX, it can't be determined how the the two dates affact the DAX without known your data model.

You DAX is very close. Check a Cumulative Sum demo for your case.

We could provide more straightforward advice if you can share a sample pbix in your case.

[dedelman_clng]

I think the issue you are having is the order of operations of the multiple filters on a single table.  Lets see if SUMX and pre-filtering the table helps out:

Cumulative Actual Revenue =
CALCULATE (
    SUMX ( FILTER ( DetailedBookingsTable, DetailedBookingsTable[Arrival Date]), DetailedBookingsTable[RV] ),
   FILTER (
        ALL ( DetailedBookingsTable[Insert Date] ),
        DetailedBookingsTable[Insert Date].[Date]
            <= MAX ( DetailedBookingsTable[Insert Date].[Date] )
    )
)

Also, please check out DAX Formatter.  This is a free service that puts your DAX into a standardized format that is easier to read.

Hope this helps,

David

[bluestonewales]

Hi,

Thanks for this, unfortunately it doesn't work as it comes up with the error below

A single value for variaton 'Date' for column 'Insert Date' in table 'DetailedBookingsTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result

To give some context to the original post. Each sale is recorded as a record in the detailed booking table including details of the product purchased (the date the product purchased is valid on) as well as the insert date that the booking was created. 

Data set is some 70,000 records, (we have data fact table which works out all of our financial periods etc which has a many to one relationship from the detailed bookings table)

Simon

[dedelman_clng]

Try changing SUMX to CALCULATE and SUM ? 

Cumulative Actual Revenue =
CALCULATE (
    CALCULATE(SUM(DetailedBookingsTable[RV]), DetailedBookingsTable[Arrival Date]),
    FILTER (
        ALL ( DetailedBookingsTable[Insert Date] ),
        DetailedBookingsTable[Insert Date].[Date]
            <= MAX ( DetailedBookingsTable[Insert Date].[Date] )
    )
)

[bluestonewales]

Hi,

Thanks but still no luck, same error 

A single value for variaton 'Date' for column 'Insert Date' in table 'DetailedBookingsTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Simon.