DAX Calculation: Average Cumulative Spend by 'Days Since'

[N_R_000]

I have a dataset of [Members] and [Orders] and I need to find the 'Average Spend Per Member', but the bit I'm struggling with is I need that same measure broken down so it can be used in a 'Cumulative Average Spend Since Membership Started'. 

Here's a mock-up of my dataset: 

The DAX I'm using for the 'Average Spend Per Member' measure is:

AVERAGEX (

SUMMARIZE (

Orders,

Orders[MembershipNo],

"MembershipAverage",

[Total Order Amount]

),

MembershipAverage

)

Let's say that shows me the average spend per Membership is £200 (example only). What I'd like to know is; what's the average spend 30 days after membership begins, or 60 days, or 90 days etc. So I'd like a line graph with 'Days Since Membership began' on the X axis, and 'Average Cumulative Spend' on the Y axis, producing a rising trend eventually reaching £200. I just can't get the DAX right to do it, any ideas?  

[v-xiaotang · Accepted solution]

Hi @

Because not clear about how your data model and table structure look like, so I create a sample.

You can take steps bellow for reference.

-

1. create a column

DaysSinceMembershipBegan column =
VAR _start =
    RELATED ( Membership[MembershipStart] )
RETURN
    DATEDIFF ( _start, ( Orders[Date] ), DAY )

2. create the measure:

if you want to divide by actual number,

then create the measure:

_average =
VAR filter_table =
    FILTER (
        ALL ( Orders ),
        Orders[DaysSinceMembershipBegan column]
            <= SELECTEDVALUE ( Orders[DaysSinceMembershipBegan column] )
    )
VAR total =
    CALCULATE ( SUM ( Orders[Value] ), filter_table )
VAR membercount =
    CALCULATE ( DISTINCTCOUNT ( Orders[MembershipNo] ), filter_table )
VAR _div =
    DIVIDE ( total, membercount )
RETURN
    _div

Result:

OR

if you want to divide by total number in membership table, create this measure

_average1 =
VAR filter_table =
    FILTER (
        ALL ( Orders ),
        Orders[DaysSinceMembershipBegan column]
            <= SELECTEDVALUE ( Orders[DaysSinceMembershipBegan column] )
    )
VAR total =
    CALCULATE ( SUM ( Orders[Value] ), filter_table )
VAR membercount =
    COUNTROWS ( Membership )
VAR _div =
    DIVIDE ( total, membercount )
RETURN
    _div

result:

 Hope this helps.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@N_R_000 , try a measure like

calculate(
AVERAGEX (
Values (Orders[MembershipNo])
[Total Order Amount]
),filter(allselected(Orders) , Orders[Date] <=max(Orders[Date]))
)