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
alex_j
Frequent Visitor

Migrating a SQL query to Power Bi (retention rates)

Hello everyone. I started to migrate some Excel reports to Power BI and one of those was the Retention Rate.

It was written in SQL and basically I have one table with all the information I want. The SQL code to generate this report is something like this: 

 

 

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='#TotalOrders' and xtype='U')
	CREATE TABLE #TotalOrders 
	(
	email VARCHAR(50),
	orderdatetime VARCHAR(50),
	shippingcountry VARCHAR(50),
	orderreference VARCHAR(50),
	fraudulent BIT
	)

INSERT INTO #TotalOrders
SELECT email, orderdatetime,shippingcountry,orderreference,fraudulent FROM ORDERS

GO

DECLARE @date AS Date
DECLARE @months AS INTEGER
-----------------------------
SET @date = CONVERT(DATETIME, '2020-08-01')
SET @months = -1

SELECT @date AS 'Date',
COUNT( distinct total.email) AS total,
COUNT( distinct retained.email) AS retained,
SUBSTRING(CAST(ROUND((1.0 * COUNT( distinct retained.email))/ (1.0 * COUNT( distinct total.email)
),4)*100 AS VARCHAR(20)),0,6) + '%' AS pcn

FROM #TotalOrders AS total
LEFT JOIN #TotalOrders AS retained
ON ( total.email = retained.email AND 
CONVERT(datetime,retained.orderdatetime, 103) BETWEEN DATEADD(Month,@months,@date)
AND @date AND retained.fraudulent = 0)

WHERE (CONVERT(datetime, total.orderdatetime, 103) BETWEEN 
	DATEADD(Month,2*@months,@date) AND DATEADD(Month, @months,@date))
	AND total.fraudulent = 0

DROP TABLE #TotalOrders

 

 

And the result is something like this: 

 

Date                    total                    retained             pcn

2020-08-01        19354                 1618                    8.360%

 

Explaining the SQL code, we have 2 variables, one for the date and the months. 

 

So, in this example, we get the emails from orders from June (months * 2) and check in July (months) how many customers bought again. 

If the months variable was -2 we would go 2 months before august (April and May) and we checked in June / July how many emails repeat themselves. 

 

How can I build something similar to this using DAX? I know it has outstanding capabilities, but I'm still getting used to all the syntax and methods. 

 

The model on Power BI has the same names and for the variables, the date would be the MIN(date) - which will get the first day of the month) and for the variable months I'm thinking about using a parameter.

 

Appreciate it everyone that could lend me a hand with this! Have a good day everyone! 

 

5 REPLIES 5
alex_j
Frequent Visitor

Thank you, both of you. The pbix file can be downloaded here.

 

Basically this sample file only has the date when the order was placed and the user email (which was hashed). Also there's a column called frauduent, which should be filter as 0, pretty simple. 

 

Once again, thank you for your help guys! 

 

Don't use a date hierarchy on the orderdatetime field. Instead, create a calculated column that only has the date value of orderdatetime, and then add a proper calendar table that holds the required granularity (day, month etc)

 

OrderDate = DATEVALUE(test[orderdatetime])
 
Calendar = CALENDARAUTO()
Month = MONTH('Calendar'[Date])
 
lbendlin_0-1599581046034.pnglbendlin_0-1599581221516.png

 

 

 

Hello lbendlin. 

 

I fixed my sample file. Actually our original file has the data table like you said and I had forgotten to create the same table on this one. The link file is the same and can be downloaded here.

 

Really hope you guys can help me on how to build the retention rate! 

v-diye-msft
Community Support
Community Support

Hi @alex_j 

 

You might consider providing your dummy pbix that would be helpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
lbendlin
Super User
Super User

please provide sample data in usable format and show expected outcome .

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.