Convert Oracle SQL Query to DAX

[PowerBIET]

Expect Output - first_shoppers = 3083

Expect Output - avg_spend_first_d = 134.53

Adding Sample Data:

report_date	channel	membership_num	demand_revenue
3/1/2023	OPIC	6923297	473.89
12/1/2022	OPIC	6923297	95.57
3/1/2023	OPIC	4732483	220.59
3/1/2023	OPIC	7968799	54.09
3/1/2023	OPIC	7707891	99.94
3/1/2023	OPIC	2953797	67.22
3/1/2023	OPIC	2119246	173.53
3/1/2023	OPIC	2159933	159.96
3/1/2023	OPIC	2276172	260.8
3/1/2023	OPIC	5549575	99.13
3/1/2023	OPIC	2109264	76.79
3/1/2023	OPIC	9386426	126.91
3/1/2023	OPIC	6798980	82.36
3/1/2023	OPIC	4165529	151.66
3/1/2023	OPIC	9276831	298.21
3/1/2023	OPIC	4771072	106.87
11/1/2022	OPIC	4771072	304.89
3/1/2023	OPIC	3956433	153.84
3/1/2023	OPIC	1650323	100.91
3/1/2023	OPIC	9976085	76.52
3/1/2023	OPIC	1061082	115.94

Full Data

https://docs.google.com/spreadsheets/d/1iiuPFVPFpS7ojtMRlEaRxQPKpR8I-8g8/edit?usp=sharing&ouid=11351... 

We have to convert this ORACLE SQL(below) query from a legacy system into dax.  Each report_date is dynamnic in the legacy system.   

This query gets first time shoppers that only placed one order is a specified range, and who have not placed any order 6 month prior starting from start day, hence the MINUS 180 part. 

How can I rewrite the query in DAX the includes the MINUS section of -180 that excludes shoppers who placed an order in Power BI?

Here is the query:

SELECT

    NVL(SUM(mem_count),0) AS first_shoppers,

    CASE WHEN SUM(revenue_d) > 0 AND SUM(mem_count) > 0 THEN SUM(revenue_d)/SUM(mem_count) ELSE 0 END avg_spend_first_d

        FROM

        ( SELECT

            COUNT( DISTINCT mbr.membership_num) AS mem_count    ,

            SUM(demand_revenue)                     AS revenue_d

                FROM product pd,

                ( SELECT membership_num FROM

                        ( SELECT membership_num, COUNT(DISTINCT order_num) AS order_count

                            FROM product

                                WHERE report_date BETWEEN '2023-03-01' AND '2023-03-02'--dynamic

                                AND channel = 'OPIC'

                                AND membership_num IS NOT NULL

                            GROUP BY membership_num

                            HAVING COUNT(DISTINCT order_num) = 1

                        )

                        MINUS

                        SELECT DISTINCT membership_num

                            FROM product

                                WHERE report_date < '2023-03-01' AND report_date >= '2023-03-02'-180 --dynamic

                                AND channel = 'OPIC'

                                AND membership_num IS NOT NULL

                ) mbr

                WHERE pd.membership_num = mbr.membership_num

                AND pd.channel = 'OPIC'

                AND report_date BETWEEN '2023-03-01' AND '2023-03-02' --dynamic

        );

[Greg_Deckler · Accepted solution]

@PowerBIET Should be something along these lines. See PBIX attached below signature. Hard to test because you only have a months worth of data.

First Shoppers = 
    VAR __DaysAgo = 180
    VAR __MinRange = MIN('Table'[report_date])
    VAR __TableRange1 = SUMMARIZE('Table',[membership_num],"__Count",COUNTROWS('Table'))
    VAR __TableRange2 = FILTER(__TableRange1, [__Count] = 1)
    VAR __PastRange = FILTER(ALL('Table'),[report_date] < __MinRange && [report_date] >= __MinRange - 180)
    VAR __FirstShoppers = DISTINCT(SELECTCOLUMNS(__TableRange2,"__MemberNum",[membership_num]))
    VAR __PastShoppers = DISTINCT(SELECTCOLUMNS(__PastRange,"__MemeberNum",[membership_num]))
    VAR __MembersOfInterest = 
        EXCEPT(
            __FirstShoppers,
            __PastShoppers
        )
    VAR __Result = COUNTROWS(__MembersOfInterest)
RETURN
   __Result

Average Spend = 
    VAR __DaysAgo = 180
    VAR __MinRange = MIN('Table'[report_date])
    VAR __TableRange1 = SUMMARIZE('Table',[membership_num],"__Count",COUNTROWS('Table'))
    VAR __TableRange2 = FILTER(__TableRange1, [__Count] = 1)
    VAR __PastRange = FILTER(ALL('Table'),[report_date] < __MinRange && [report_date] >= __MinRange - 180)
    VAR __FirstShoppers = DISTINCT(SELECTCOLUMNS(__TableRange2,"__MemberNum",[membership_num]))
    VAR __PastShoppers = DISTINCT(SELECTCOLUMNS(__PastRange,"__MemeberNum",[membership_num]))
    VAR __MembersOfInterest = 
        EXCEPT(
            __FirstShoppers,
            __PastShoppers
        )
    VAR __Result = AVERAGEX(FILTER('Table', [membership_num] IN __MembersOfInterest),[demand_revenue])
RETURN
    __Result

[PowerBIET]

Thanks, this below ended up being my final code, after adding a relative date slicer.

Shoppers =

    VAR __MinRange = CALCULATE( MIN(Cal[Date]) )

    VAR __MaxRange = CALCULATE( max(Cal[Date]) )

    VAR __TableRange1 =  SUMMARIZE('Table',[membership_num],"__Count", DISTINCTCOUNT('Table'[order_num]) )

    var a  =  CALCULATETABLE( VALUES('Table'[membership_num]), FILTER(__TableRange1,[__Count]=1) )

    var b = CALCULATETABLE( VALUES('Table'[membership_num]),

                           FILTER(ALL('Cal'),[Date] >= __MaxRange - 180 && [Date] < __MinRange ) )

    VAR __MembersOfInterest =

        EXCEPT(

            a,

            b

        )

    VAR __Result = COUNTROWS(__MembersOfInterest)

RETURN

   __Result