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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mark_endicott
Solution Specialist
Solution Specialist

Averagex calc for all customers, counting events between Min date and computed date

Hello, 

 

I'm attempting to count the Average number of emails customers customers receive in their first N months. To do this, I am defining their MIN date, and shifting this on a number of months using EDATE and a "what if parameter" that provides the number of months. 

 

My measure works when filtering by customers, but when it computes the average over all customers, it is using the MIN date, regardless of customer (i.e. the first date in the table), rather than using each customer's MIN date to compute the average - Despite being wrapped in an averagex.

 

I'm hoping this is an easy oversight in my filter context, and will try to highlight my issue in screenshots, with the DAX published at the bottom of this post.

 

With the screenshots, please see the mmm_yyyy table in the middle of the image, with no customers selected I would expect this to have all months & an average number of emails per any customer whose first email was in that month. Instead it only shows only September & October 2016 as my data begins on 01/09/2016.

 

With Customers selected:

mark_endicott_0-1665499699784.png

 

With no (or all)) Customers Selected:

mark_endicott_1-1665500013484.png

 

 

// Provide customers to be iterated through during Averagex
VAR selected_customers =
    ALLSELECTED( 'Campaigns'[email] )
RETURN
    AVERAGEX (
            selected_customers,
            // create min date for each customer - removing filters for product, campaign type, campaign outcome & date, keeping customer email context
            // EDATE shifts the min date on a specified number of months
            VAR date_from_sign_up =
                CALCULATE (
                    EDATE ( MIN ( 'Campaigns'[date_key] ), [Number of Months Value] ),
                    REMOVEFILTERS ( 'Campaigns' ),
                    VALUES ( 'Campaigns'[email] )
                )
            // Count all emails before the date specified above
            VAR calc =
                CALCULATE (
                    [No of Recipients],
                    KEEPFILTERS ( 'Campaigns'[date_key] <= date_from_sign_up )
                )
            RETURN
                calc
        )

 

 

2 REPLIES 2
johnt75
Super User
Super User

try

num emails =
// Provide customers to be iterated through during Averagex
VAR selected_customers =
    ALLSELECTED ( 'Campaigns'[email] )
RETURN
    AVERAGEX (
        selected_customers,
        // create min date for each customer - removing filters for product, campaign type, campaign outcome & date, keeping customer email context
        VAR CurrentCustomer = 'Campaigns'[email] // EDATE shifts the min date on a specified number of months
        VAR date_from_sign_up =
            CALCULATE (
                EDATE ( MIN ( 'Campaigns'[date_key] ), [Number of Months Value] ),
                REMOVEFILTERS ( 'Campaigns' ),
                TREATAS ( { CurrentCustomer }, 'Campaigns'[email] )
            ) // Count all emails before the date specified above
        VAR calc =
            CALCULATE (
                [No of Recipients],
                KEEPFILTERS ( 'Campaigns'[date_key] <= date_from_sign_up )
            )
        RETURN
            calc
    )

@johnt75 - Thanks for the assistance, I would love to tell you if this solution has worked, but I'm afraid it is giving me a timeout error on the visuals. I see what you're trying to do though.

 

Can you think of anything else I can test?

 

Using your idea I've tried various other options of defining the CurrentCustomer (SELECTEDVALUE, VALUES, SUMMARIZE) but these have either produced a similar problem (SELECTEDVALUE, VALUES), or have not worked (SUMMARIZE). 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.