Forum Discussion
Aggregate a distinct user count by first purchase date grouping
Microsoft EmployeeHi 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
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
- Ashish_Mathur
Super User
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.
- 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
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
