Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
PowerBIET
Frequent Visitor

SQL to Dax conversion

Please help me convert this SQL query from a legacy system into dax.  I will be using the BETWEEN date slicer in Power BI with report_date.  The report_date is dynamic in the legacy system. The SQL Query gets the repeat shoppers who made a purchase in the last 180 days.   I am confused how to convert it to dax.   This query is a different query from the last post.

 

Sample Power File with Data:

https://drive.google.com/file/d/1SHFMwz53srJg3pwjQI4a7grBMyOGS-Ph/view?usp=sharing

 

SQL Data:

SELECT
    NVL(SUM(mem_count),0) AS repeat_shoppers,
    CASE WHEN SUM(revenue_d) > 0 AND SUM(mem_count) > 0 THEN SUM(revenue_d)/SUM(mem_count) ELSE 0 END avg_spend_repeat_d
        FROM
        (SELECT
            COUNT( DISTINCT mbr.membership_number)  AS mem_count    ,
            SUM(demand_revenue)                     AS revenue_d
                FROM dw_summary.ddh_product_detail_new pd,
                (   ( SELECT membership_number FROM(        
                            SELECT membership_number, COUNT(DISTINCT order_number) AS order_count
                                FROM dw_summary.ddh_product_detail_new
                                    WHERE report_date BETWEEN '2023-02-02' AND '2020-09-19' --dynamic
                                        AND digital_channel = 'OPIC'
                                        AND membership_number IS NOT NULL
                                    GROUP BY membership_number
                                    HAVING COUNT(DISTINCT order_number) > 1)
                            MINUS
                            SELECT DISTINCT membership_number
                                FROM dw_summary.ddh_product_detail_new
                                    WHERE report_date < '2020-02-02' AND report_date >= '2020-02-02'-180 --dynamic
                                        AND digital_channel = 'OPIC'
                                        AND membership_number IS NOT NULL  
                    )          
                    UNION
                    (   SELECT DISTINCT membership_number
                            FROM dw_summary.ddh_product_detail_new
                                WHERE report_date BETWEEN '2020-02-02' AND '2020-09-19' --dynamic
                                AND digital_channel = 'OPIC'
                                AND membership_number IS NOT NULL
                        INTERSECT
                        SELECT DISTINCT membership_number
                            FROM dw_summary.ddh_product_detail_new
                                WHERE report_date < '2020-02-02' AND report_date >= '2020-02-02'-180 --dynamic
                                AND digital_channel = 'OPIC'
                                AND membership_number IS NOT NULL
                    )
                ) mbr
                WHERE pd.membership_number = mbr.membership_number
                AND pd.digital_channel = 'OPIC'
                AND report_date BETWEEN '2020-02-02' AND '2020-09-19' --dynamic
        );
1 REPLY 1
amitchandak
Super User
Super User

@PowerBIET , I always prefer, we should take a problem and solve it Power BI way, then query

So for repeat, new, lost, and did not appear in the last few days, can be worked using the logic given below

 

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

 

Customer Retention Part 4:Customers to Retain- Segment in 4 quadrant based on Margin % and Discount: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-4-Customers-to-Retain-Segmen...

 

For rolling

 

Rolling Days Formula: https://youtu.be/cJVj5nhkKBw

 

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.