Aggregate a distinct user count by first purchase date grouping

[jdixon41]

I have a problem that I haven't been able to solve in many hours of trying.

Attached is a PBIX file with some sample data.  The real data comes from SSAS Tabular if that matters to the solution. (I can't add tables for one off reports to the SSAS model)

Sample Data

Users table with:

UserKey

Approved On Date

Sales Orders table with:

SalesKey

UserKey

Sales Order Date

Calendar table with:

Date

I'd like to develop a report with Date as a column, then a column for PurchasedMonth1, and subsequent columns for PurchasedMonth2, PurchasedMonth3, etc.

The result for 'PurchasedMonth1' would be, All the Unique UserKeys that made a sale where the DateDiff between Users[Approved On Date] and their MIN(Sales Orders[Sales Order Date]) is less than 30.

For subsequent 'PurchasedMonths', like the 2nd one. I'd like to not count a second sale by the user. (hence the desire to get their min date sale)

I've tried many different things, but logically I'd like a new table that roles up into distinct, non blank UserKeys:

UserKey, Approved On Date, DateOfFirstPurchase, DaysUntilFirstPurchase

UserKey would be the unique userkey with a related purchase

Approved On Date would come along with that userkey

DateOfFirstPurchase would be a MIN of Sales Orders[Sales Order Date]

DaysUntilFIrstPurchase woudl be DateDiff of Users[Approved On Date] and Sales Orders[Date of FirstPurchase] (skipping errors)

Then aggregate the counts of those userKeys for each time bucket measure. (PurchasedMonth1, PurchasedMonth2, etc.)

A confunding factor is some SalesOrderDates are Less Than Approved On dates. I need to error handle these rows and remove them from aggregations.

The best I've came up with is the following. But when I move to aggregating the PurchasedMonth2, it won't remove the users that were counted in Month 1.  Against the sample report I'd like to see only 1 count since only 1 user made his first Purchase in this bucket (30 to 60 days)

 PurchaedMonth2
     = CALCULATE (
         COUNTROWS (
             SUMMARIZE (
                 FILTER (
                     ADDCOLUMNS (
                         'Sales Orders',
                         "DaysToPurchase", IFERROR (
                             DATEDIFF (
                                 RELATED ( Users[Approved On Date] ),
                                 'Sales Orders'[Sales Order Date],
                                 DAY
                             ),
                             -1
                         )
                     ),
                     [DaysToPurchase] > 30
                         && [DaysToPurchase] <= 60
                 ),
                 'Sales Orders'[UserKey],
                 "FirstSaleDate", MINX ( 'Sales Orders', 'Sales Orders'[Sales Order Date] )
             )
         ),
         USERELATIONSHIP ( 'Calendar'[Date], Users[Approved On Date] )
     ) 

Thanks in advance for guidance. 

-JD

[v-jiascu-msft]

Hi @jdixon41,

You can add two calculated columns to table Sales Orders first. 

IfFirstPurchase =
VAR CurrentUserkey = [UserKey]
VAR FirstPurchaseDate =
    CALCULATE (
        MIN ( 'Sales Orders'[Sales Order Date] ),
        FILTER (
            'Sales Orders',
            'Sales Orders'[Sales Order Date] >= RELATED ( Users[Approved On Date] )
                && 'Sales Orders'[UserKey] = CurrentUserkey
        )
    )
RETURN
    IF ( [Sales Order Date] = FirstPurchaseDate, 1, 0 )

PurchasedMonth =
VAR days =
    IF (
        [IfFirstPurchase] = 1,
        DATEDIFF ( RELATED ( Users[Approved On Date] ), [Sales Order Date], DAY ),
        0
    )
RETURN
    IF (
        [IfFirstPurchase] = 1,
        IF (
            days <= 30,
            "PurchaedMonth1",
            IF (
                days <= 60,
                "PurchaedMonth2",
                IF (
                    days <= 90,
                    "PurchaedMonth3",
                    IF ( days <= 120, "PurchaedMonth4", "Others" )
                )
            )
        ),
        "NotFirstPurchase"
    )

Please check the file here: https://1drv.ms/u/s!ArTqPk2pu-BkgTDUKzgH5w6uAC5k.

Best Regards!

Dale

[jdixon41]

@Ashish_Mathur

Here is a sample PBIX file.

Here is an image of expected output:

In the image there is some example tables to the left.

To the right top, that is the expected out put.

To the right bottom, that is the DAX table i'm trying to create with DAX table functions (filter, calculatetable,etc.). But I can't simply create that table in a formula and reference it for the decisions i'm trying to make. 

The problem is my company is using an SSAS Tabular model and I don't want to add entire columns for one-off reports. (perhaps i'm not understanding how to use SSAS Tabular and Power Bi to report on data in related tables?)

-- back to the image -- 

The Orange is July, the Blue is August.

I'd like the output to find a count of the unique UserKeys ApprovedOn July, that purchased in Purchased Month1, Purchased Month2, ..etc.

You can see in the output table, that the green cell shows the count of users that purchased within 30 days of the Approved On Date. (DateDiff of Users[ApprovedOn] , SalesOrders[Sales Order Date], days). I'd like this output to be a count of the unique UserKeys that match the critiera.

The Grey are the counts for the PurchasedMonth2. Again I'd like the results to be Unique UserKey counts.

The final bit of trickyness is if someone purchased in Month1, I don't want to count him in Month2.

Plus some SalesOrderDates are BEFORE approvedOn, which throws and error. So I want to exclude those.

@v-jiascu-msft For now i've used a similar solution that you've suggested to add columns. However I don't want to make a habit of adding columns to my model for one-off reports right?  I'm using SSAS Tabular so when I connect to Power BI all the options to make new tables or Columns are grayed out. I'd have to add it all to the SSAS tabular model which seems like bad practice.

(Screenshot to show that once the tabular model is loaded i can't make new columns or tables for the one-off report)

 

Thank you very much for the help. Maybe it's impossible to use DAX to create these measures without helper columns?

(i come from a SQL background and DAX is quite different)

-JD

[v-jiascu-msft]

Hi @jdixon41,

1. You have to change the relationships. Activate another one.

2. 30 days, 60 days has a measure each.

3. There is an error in your expected output. The Purchased Month 2 of August should be 1 due to the sales 2004.

Purchased Month 1 =
SUMX (
    ADDCOLUMNS (
        'Sales Orders',
        "days", 'Sales Orders'[Sales Order Date] - RELATED ( Users[Approved On Date] )
    ),
    IF ( [days] >= 0 && [days] <= 30, 1, 0 )
)

Purchased Month 2 =
SUMX (
    ADDCOLUMNS (
        'Sales Orders',
        "days", 'Sales Orders'[Sales Order Date] - RELATED ( Users[Approved On Date] )
    ),
    IF ( [days] > 30 && [days] <= 60, 1, 0 )
)

Best Regards!

Dale

[jdixon41]

Hi @v-jiascu-msft,

I only wanted to count the first purchase for each user. Not all the subsequent purchases.  So the idea of the report is to provide insight into how long it takes Users to make their first purchase once approved. We've made some changes to our discounting efforts and we want to see if those 'first purchase' rates are slowing down.

That's why the SalesKey 2004 shouldn't be counted.  Nor should SalesKey 2002 (because there is an error in the data).

The way I wanted to approach this problem is envision the final table (referenced in my image on my last post), and then do the DAX functions to fetch the results I want.  I don't think that approach is correct because I couldn't apply the functions I wanted to this imaginary filtered table that joins information across two related ones.  To be clear I didn't want to create a new table in my model, I wanted to nest CALCULATETABLE, FILTER, SUMMARIZE, ADDCOLUMNS, etc. to get this information together before doing something like COUNTROWS.

I'm very new to DAX and this problem feels very difficult.  Adding to all this is my model is in SSAS and we don't want to compound a bunch of one-off columns or tables for every report we are asked to do.

Is the only solution to continually add columns to the model that will only be used by single reports?  (This problem becomes straightforward if I add a new column to my Users table called, "FirstPurchaseDate".)  I'd like to learn how to solve this purely with DAX instead of relying on adding columns for every hiccup I have with a report request.

-JD

[Ashish_Mathur]

Hi,

I will need to crate a seperate calendar table to solve this problem.  If that is something your entire process can allow, then i can try to solve this problem.  Also, in cell J20, shouldn't the date of first purchase be 8/16/2017?  Or do you want to show here the date of first purchase after the ApprovedOn date?

Please clarify both questions above.

[Ashish_Mathur]

Hi,

Share a small dataset and show the expected result.