Aggregate a distinct user count by first purchase date grouping

Question

I have a problem that I haven't been able to solve in many hours of trying.&nbsp;Attached is a PBIX file with some sample data.&nbsp; 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&nbsp;Users table with:UserKeyApproved On Date&nbsp;Sales Orders table with:SalesKeyUserKeySales Order Date&nbsp;Calendar table with:Date&nbsp;I'd like to develop a report with Date as a column, then a column for PurchasedMonth1, and subsequent columns for PurchasedMonth2, PurchasedMonth3, etc.&nbsp;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.&nbsp;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)&nbsp;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&nbsp;UserKey would be the unique userkey with a related purchaseApproved On Date would come along with that userkeyDateOfFirstPurchase 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)&nbsp;Then aggregate the counts of those userKeys for each time bucket measure. (PurchasedMonth1, PurchasedMonth2, etc.)&nbsp;A confunding factor is some SalesOrderDates are Less Than Approved On dates. I need to error handle these rows and remove them from aggregations.&nbsp;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.&nbsp; 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)&nbsp; PurchaedMonth2 &nbsp;&nbsp;&nbsp;&nbsp;= CALCULATE ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COUNTROWS ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ADDCOLUMNS ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Sales Orders', &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"DaysToPurchase", IFERROR ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DATEDIFF ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RELATED ( Users[Approved On Date] ), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Sales Orders'[Sales Order Date], &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DAY &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[DaysToPurchase] &gt; 30 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp; [DaysToPurchase] &lt;= 60 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Sales Orders'[UserKey], &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"FirstSaleDate", MINX ( 'Sales Orders', 'Sales Orders'[Sales Order Date] ) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;USERELATIONSHIP ( 'Calendar'[Date], Users[Approved On Date] ) &nbsp;&nbsp;&nbsp;&nbsp;) &nbsp;&nbsp;Thanks in advance for guidance.&nbsp;&nbsp;-JD

ashish_mathur · Answer

Hi,&nbsp;Share a small dataset and show the expected result.

v-jiascu-msft · Answer

Hi&nbsp;jdixon41,&nbsp;You can add two calculated columns to table Sales Orders first.&nbsp;IfFirstPurchase =
VAR CurrentUserkey = [UserKey]
VAR FirstPurchaseDate =
    CALCULATE (
        MIN ( 'Sales Orders'[Sales Order Date] ),
        FILTER (
            'Sales Orders',
            'Sales Orders'[Sales Order Date] &gt;= RELATED ( Users[Approved On Date] )
                &amp;&amp; '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 &lt;= 30,
            "PurchaedMonth1",
            IF (
                days &lt;= 60,
                "PurchaedMonth2",
                IF (
                    days &lt;= 90,
                    "PurchaedMonth3",
                    IF ( days &lt;= 120, "PurchaedMonth4", "Others" )
                )
            )
        ),
        "NotFirstPurchase"
    )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Please check the file here:&nbsp;https://1drv.ms/u/s!ArTqPk2pu-BkgTDUKzgH5w6uAC5k.&nbsp;Best Regards!Dale

jdixon41 · Answer

Ashish_Mathur&nbsp;Here is a sample PBIX file.&nbsp;Here is an image of expected output:&nbsp;&nbsp;In the image there is some example tables to the left.&nbsp;To the right top, that is the expected out put.&nbsp;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.&nbsp;&nbsp;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?)&nbsp;-- back to the image --&nbsp;&nbsp;The Orange is July, the Blue is August.&nbsp;I'd like the output to find a count of the unique UserKeys ApprovedOn July, that purchased in Purchased Month1, Purchased Month2, ..etc.&nbsp;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.&nbsp;The Grey are the counts for the PurchasedMonth2. Again I'd like the results to be Unique UserKey counts.&nbsp;The final bit of trickyness is if someone purchased in Month1, I don't want to count him in Month2.&nbsp;Plus some SalesOrderDates are BEFORE approvedOn, which throws and error. So I want to exclude those.&nbsp;&nbsp;v-jiascu-msft&nbsp;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?&nbsp; 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.&nbsp;(Screenshot to show that once the tabular model is loaded i can't make new columns or tables for the one-off report)&nbsp;&nbsp;Thank you very much for the help. Maybe it's impossible to use DAX to create these measures without helper columns?&nbsp;(i come from a SQL background and DAX is quite different)&nbsp;-JD

ashish_mathur · Answer

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

v-jiascu-msft · Answer

Hi&nbsp;jdixon41,&nbsp;1. You have to change the relationships. Activate another one.2. 30 days, 60 days&nbsp;has a measure each.3. There is an error in your expected output. The Purchased&nbsp;Month 2 of August&nbsp;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] &gt;= 0 &amp;&amp; [days] &lt;= 30, 1, 0 )
)Purchased Month 2 =
SUMX (
    ADDCOLUMNS (
        'Sales Orders',
        "days", 'Sales Orders'[Sales Order Date] - RELATED ( Users[Approved On Date] )
    ),
    IF ( [days] &gt; 30 &amp;&amp; [days] &lt;= 60, 1, 0 )
)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Best Regards!Dale

Forum Discussion

Aggregate a distinct user count by first purchase date grouping

6 Replies

Related content

distinct count aggregate

count distinct

Aggregating conditional distinct counts

Microsoft fabric license purchase

Data Aggregation issue