Convertir Oracle SQL Query a DAX

[Syndicate_Admin]

Salida esperada - first_shoppers = 3083

Salida esperada - avg_spend_first_d = 134.53

Agregar datos de ejemplo:

report_date	canal	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

Datos completos

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

Tenemos que convertir esta consulta ORACLE SQL (abajo) de un sistema heredado a dax. Cada report_date es dinámico en el sistema heredado.

Esta consulta obtiene compradores por primera vez que solo realizaron un pedido en un rango especificado y que no han realizado ningún pedido 6 meses antes a partir del día de inicio, de ahí la parte MENOS 180.

¿Cómo puedo volver a escribir la consulta en DAX que incluye la sección MENOS de -180 que excluye a los compradores que realizaron un pedido en Power BI?

Aquí está la consulta:

ESCOGER

NVL(SUMA(mem_count),0) COMO first_shoppers,

CASO CUANDO SUMA(revenue_d) > 0 Y SUMA(mem_count) > 0 ENTONCES SUMA(revenue_d)/SUMA(mem_count) MÁS 0 FIN avg_spend_first_d

DE

( ESCOGER

CONTAR( DISTINTO mbr.membership_num) COMO mem_count ,

SUMA(demand_revenue) COMO revenue_d

DE producto pd,

( ESCOGER membership_num DE

( ESCOGER membership_num, CONTAR(DISTINTO order_num) COMO order_count

DE producto

DÓNDE report_date ENTRE '2023-03-01' Y '2023-03-02'--dinámico

Y canal = 'OPIC'

Y membership_num NO ES NULL

AGRUPAR POR membership_num

TENIENTE CONTAR(DISTINTO order_num) = 1

)

MENOS

SELECCIONAR DISTINTO membership_num

DE producto

DÓNDE report_date < '2023-03-01' Y report_date >= '2023-03-02'-180 --dinámico

Y canal = 'OPIC'

Y membership_num NO ES NULL

) MBR

DÓNDE pd.membership_num = mbr.membership_num

Y pd.channel = 'OPIC'

Y report_date ENTRE '2023-03-01' Y '2023-03-02' --dinámico

);

[Syndicate_Admin]

@PowerBIET Debería ser algo en este sentido. Consulte PBIX adjunto debajo de la firma. Difícil de probar porque solo tiene un mes de datos.

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

[Syndicate_Admin]

Gracias, esto a continuación terminó siendo mi código final, después de agregar una segmentación de datos relativa.

Compradores =

VAR __MinRange = CALCULAR( MIN(Cal[Fecha]) )

VAR __MaxRange = CALCULAR( max(Cal[Fecha]) )

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 ) )

DONDE __MembersOfInterest =

EXCEPTUAR(

un

b

)

VAR __Result = COUNTROWS(__MembersOfInterest)

DEVOLUCIÓN

__Resultado