Customers who spent $X QTD after $Date

[tachydidaxy]

After receiving very helpful advice from @Ashish_Mathur on a previous post I find myself in need of help again.

I'm hoping to count the number of customers who have spent a certain amount of money ($90) per quarter by a certain date. I need to count only the customers who have spent greater than $90 after seven days before the end of a given quarter.

I believe I have all the components needed to complete. I have a measure which gives me the total spent by the customer.

FandB Member Spending :=
CALCULATE (
    SUMX ( FBChecks, 'FBChecks'[amount] ),
    FILTER (
        FBChecks,
        RELATED ( 'Type'[member_type_class] ) = "Social"
            || RELATED ( 'Type'[member_type_class] ) = "Comprehensive"
    )
)

I have a measure that gives me a running total/cumulative total for their spending:

QTD :=
IF (
    [FandB Member Spending],
    TOTALQTD ( [FandB Member Spending], Calendar[dates] )
)

Again thanks to @Ashish_Mathur I have the measures that count the number of customers who have spent $90 or more in a given quarter:

Spent $90 or More :=
IF (
    HASONEVALUE ( Calendar[Year] ) && NOT ( HASONEFILTER ( Calendar[dates] ) ),
    CALCULATE (
        SUMX ( VALUES ( Calendar[Quarter] ), [Member Count with FB Spending Over 90] )
    )
)

Member Count with FB Spending Over 90:=COUNTROWS (
    FILTER (
        SUMMARIZE (
            FILTER (
                FBChecks,
                RELATED('Type'[member_type_class]) = "Comprehensive"
                    || RELATED('Type'[member_type_class]) = "Social"
            ),
            FBChecks[member_id],
            "rolled_sum", SUM ( FBChecks[amount] )
        ),
        [rolled_sum] > 90
    )
)

 And lastly, I have a measure that returns the cut-off date we're currently using (seven days prior to the end of the current context's quarter):

Last Quarter Date Minus 7 :=
IF (
    [FandB Member Spending] && HASONEVALUE ( Calendar[Quarter] ),
    ENDOFQUARTER ( Calendar[dates] ) - 7
)

The desired visual report is a simple pivot table that has years, quarters, and member id on the rows. I've filtered my current pivot table to shown only 2017-Q3 for simplicity. I've also filitered the member ids to show five (5) customers who fit the criteria needed. As you can see from the screen grab below, the highlighted rows for each member id in question show the date that they actually broke the $90 mark and that date is past the end of quarter minus seven days.

 So with all that being said, how should I approach this? I'd like to be able to have a measure that worked without having the individual calendar days on the rows if at all possible. I'd like to have it mirror the behavior of the "Spent $90 or More" column shown in the screen grab above.

Please find link here for sample workbook with all the measures etc. I'm working on Sheet 2 of the workbook.

Any advice would be appreciated.

[Ashish_Mathur]

Hi,

Try this calculated field formula

=COUNTROWS(FILTER(SUMMARIZE('Calendar','Calendar'[dates],"ABCD",CALCULATE(MAX('Calendar'[dates]),ALL('Calendar'[dates]))-MAX('Calendar'[dates])),[ABCD]<=7&&[QTD]>=90))

Download the file from here.  Refer to the worksheet named Ashish solution.

Hope this helps.

[tachydidaxy]

Thanks @Ashish_Mathur !!!! That is definitely closer to what I need than anything I came up with.

Unfortunately, though it works perfectly for the example member ids I filtered for in my previous sample data (38, 130, 274, 280 and 320) it does not appear to work correctly for all accounts.

For instance, member id #313 is giving me a tally of 8 and I'm not quite sure why, I've highlighted the rows of which it's counting for the member id under the column titled "Ashihs Formula" (see screen grab below):

As you can see it's counting rows like the row for date 7/26/17. I don't understand why it would though because it should fail both tests that you set up in your formula (subtracting the max date from the current row's context from the max date from all dates (which is essentially the EoQ) is NOT <= 7). So I don't understand why that row is currently being counted.

I do understand why the last three rows have been counted as their dates are all on or after the cut off date (9/23/17, 9/27/17 and 9/28/17 respectively) and their QTD totals are greater than $90.

I've created some additional helper columns for me to try and dissect how your formula is working, but am still at a loss why these particular rows are being counted.

Can you provide some insight? Here's the latest workbook which includes the helper columns I referenced.

[Ashish_Mathur]

Hi @tachydidaxy,

This certainly has to do something with the version of the PowerPivot.  I see the answer as 3 in my file (not 8 as i see in your sccreenshot).  Infact, when i open your file, i get a message saying that this was prepared in a previous vesion of the PowerPivot and the model needs to be upgraded.  When i do that, i see 3 as the answer.