Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

AVG New Customer Spend - DAX

Hi,

 

I need to calculate the average spend for new customers in the previous month. However, it needs to be only for customers that have had at least 2 visits in that previous month. 

 

Formula: Spend of new customers with >=2 visits in previous month / no. of new customers with >=2 visits in previous month

 

Fact table below - it also has a column for spend

 

Fact table:

 

 

Date dimension is joined on visit date. 

 

New customer is defined by their reg date. So the dax should count customers that registered and had two visits in the previous full month. 

From the above, only customer 1 should be counted, as customer 2 didn't have a registration date in the previous month.

 

Any help would be appreciated. Thanks!

AVG
DAX
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    Maybe you can try this code:

    AVG =
AVERAGEX (
    FILTER (
        CALCULATETABLE (
            SUMMARIZE (
                FILTER ( 'FactTable', MONTH ( [Reg_date] ) = MONTH ( [Visits date] ) ),
                [Customer_ID],
                "count", IF ( COUNTROWS ( 'FactTable' ) >= 2, 1, 0 ),
                "spend", SUM ( FactTable[Spend] )
            ),
            PREVIOUSMONTH ( 'Calendar'[Date] )
        ),
        [count] <> 0
    ),
    [spend]
)

    Result:

     

    Pbix file in the end you can refer.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Bumping the post. Can anyone help? Thanks

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Maybe you can try this code:

    AVG =
AVERAGEX (
    FILTER (
        CALCULATETABLE (
            SUMMARIZE (
                FILTER ( 'FactTable', MONTH ( [Reg_date] ) = MONTH ( [Visits date] ) ),
                [Customer_ID],
                "count", IF ( COUNTROWS ( 'FactTable' ) >= 2, 1, 0 ),
                "spend", SUM ( FactTable[Spend] )
            ),
            PREVIOUSMONTH ( 'Calendar'[Date] )
        ),
        [count] <> 0
    ),
    [spend]
)

    Result:

     

    Pbix file in the end you can refer.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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