Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
N_R_000
Helper I
Helper I

DAX Calculation: Average Cumulative Spend by 'Days Since'

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: 

N_R_000_0-1620136432788.png

 

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?  

 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

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,

v-xiaotang_3-1620294762974.png

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:

v-xiaotang_1-1620294716237.png

 

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:

v-xiaotang_2-1620294716241.png

 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.

View solution in original post

2 REPLIES 2
v-xiaotang
Community Support
Community Support

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,

v-xiaotang_3-1620294762974.png

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:

v-xiaotang_1-1620294716237.png

 

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:

v-xiaotang_2-1620294716241.png

 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
Super User
Super User

@N_R_000 , try a measure like

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.