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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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