Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago
Solved

Consulta SQL a DAX

Hola a todos Necesito ayuda para convertir una consulta SQL a DAX con un ligero cambio. La siguiente consulta me dará lo que necesito para el mes de abril, pero lo que necesito es que mi DA...
  • Syndicate_Admin's avatar
    Syndicate_Admin
    5 years ago

    @Kartiklal03

    Los modifiqué:

    Bookings = 

var __startdate = min( 'Calendar'[Date Key] )
var __enddate = max( 'Calendar'[Date Key] )  
return

CALCULATE (
    DISTINCTCOUNT ( Booking[Booking Number] ),
        FILTER(
            ALL(Booking),
            Booking[Booking Date] >= __startdate &&
            Booking[Booking Date] <= __enddate  &&

            OR(
                NOT( Booking[Cancellation Date] >= __startdate &&
                Booking[Cancellation Date] <= __enddate ),
                ISBLANK( Booking[Cancellation Date] )
            )
            &&
            OR(
                NOT( Booking[Expiry Date] >= __startdate &&
                Booking[Expiry Date] <= __enddate ),
                ISBLANK( Booking[Expiry Date] )
            )
            
        ),  
        Booking[Channel Name] = "Internet"&&
        Booking[Original Booking Status] IN {"Firm", "Normal", "Requested", "OP"}
)

    Revenue = 

var __startdate = min( 'Calendar'[Date Key] )
var __enddate = max( 'Calendar'[Date Key] )  
return

CALCULATE (
    sum ( Booking[Booking Price EUR] ),
        FILTER(
            ALL(Booking),
            Booking[Booking Date] >= __startdate &&
            Booking[Booking Date] <= __enddate  &&

            OR(
                NOT( Booking[Cancellation Date] >= __startdate &&
                Booking[Cancellation Date] <= __enddate ),
                ISBLANK( Booking[Cancellation Date] )
            )
            &&
            OR(
                NOT( Booking[Expiry Date] >= __startdate &&
                Booking[Expiry Date] <= __enddate ),
                ISBLANK( Booking[Expiry Date] )
            )

        ),  
        Booking[Channel Name] = "Internet"&&
        Booking[Original Booking Status] IN {"Firm", "Normal", "Requested", "OP"}
)
Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

@Kartiklal03

Debe proporcionar más información sobre el modelo y cómo desea visualizarlo. ¿Tienes una tabla de calendario?

Mejor, comparta un archivo PBIX de ejemplo.


  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    5 years ago

    @Kartiklal03

    Por favor, compruebe mi respuesta original, modifiqué la fórmula y adjunto el archivo.


    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      5 years ago

      No @Fowmy,

      Gracias por eso.

      ¿Cómo incluo "si la fecha de cancelación o caducidad está en blanco"? Estoy teniendo algunos problemas con esto.

      Modifiqué el DAX para incluir si la fecha de cancelación o la fecha de caducidad están en blanco, pero no me está dando la cifra correcta. 

      Bookings :=
VAR __startdate =
    MIN ( 'Calendar'[Date Key] )
VAR __enddate =
    MAX ( 'Calendar'[Date Key] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Booking[Booking Number] ),
        FILTER (
            ALL ( Booking ),
            Booking[Booking Date] >= __startdate
                && Booking[Booking Date] <= __enddate
                && NOT ( Booking[Cancellation Date] >= __startdate
                    && Booking[Cancellation Date] <= __enddate )
                    || ISBLANK ( Booking[Cancellation Date] )
                        && NOT ( Booking[Expiry Date] >= __startdate
                            && Booking[Expiry Date] <= __enddate )
                            || ISBLANK ( Booking[Expiry Date] )
        ),
        Booking[Channel Name] = "Internet",
        Booking[Original Booking Status] = "FIRM"
            || Booking[Original Booking Status] = "Normal"
            || Booking[Original Booking Status] = "OP"
            || Booking[Original Booking Status] = "Requested"
    )

      Kartiklal03_0-1626271211459.png

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Administrator
        5 years ago

        @Kartiklal03

        Los modifiqué:

        Bookings = 

var __startdate = min( 'Calendar'[Date Key] )
var __enddate = max( 'Calendar'[Date Key] )  
return

CALCULATE (
    DISTINCTCOUNT ( Booking[Booking Number] ),
        FILTER(
            ALL(Booking),
            Booking[Booking Date] >= __startdate &&
            Booking[Booking Date] <= __enddate  &&

            OR(
                NOT( Booking[Cancellation Date] >= __startdate &&
                Booking[Cancellation Date] <= __enddate ),
                ISBLANK( Booking[Cancellation Date] )
            )
            &&
            OR(
                NOT( Booking[Expiry Date] >= __startdate &&
                Booking[Expiry Date] <= __enddate ),
                ISBLANK( Booking[Expiry Date] )
            )
            
        ),  
        Booking[Channel Name] = "Internet"&&
        Booking[Original Booking Status] IN {"Firm", "Normal", "Requested", "OP"}
)

        Revenue = 

var __startdate = min( 'Calendar'[Date Key] )
var __enddate = max( 'Calendar'[Date Key] )  
return

CALCULATE (
    sum ( Booking[Booking Price EUR] ),
        FILTER(
            ALL(Booking),
            Booking[Booking Date] >= __startdate &&
            Booking[Booking Date] <= __enddate  &&

            OR(
                NOT( Booking[Cancellation Date] >= __startdate &&
                Booking[Cancellation Date] <= __enddate ),
                ISBLANK( Booking[Cancellation Date] )
            )
            &&
            OR(
                NOT( Booking[Expiry Date] >= __startdate &&
                Booking[Expiry Date] <= __enddate ),
                ISBLANK( Booking[Expiry Date] )
            )

        ),  
        Booking[Channel Name] = "Internet"&&
        Booking[Original Booking Status] IN {"Firm", "Normal", "Requested", "OP"}
)