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
mark_endicott
Helper II
Helper II

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
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.

Top Solution Authors