SQL Query to DAX

[Anonymous]

Hi All, 

I need help converting a SQL query to DAX with a slight change. 

The following query will give me what I need for the month of April but what I need is for my DAX to be dynamic based on whatever month is selected. 

So, if my current month is March for example, the DAX should give me bookings for booking date between 20210301 and 20210330 and where Cancellation Date is not between 20210301 and 20210330 and Expiry Date is not between 20210301 and 20210330. And similarly if any other month is selected. Hope this makes sense!

Thanks for your help!

[Fowmy]

@Anonymous 

Hi, Insert a Slicer on the report and include the Booking Date and filter to April. Then add the following two measures:

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 )
            &&
            NOT( Booking[Expiry Date] >= __startdate &&
            Booking[Expiry Date] <= __enddate )
        ),  
        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  &&

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

[Anonymous]

Hi @Fowmy , 

Thanks for your reply but I dont think that will give me what I'm looking for. 

I need to be able to display my data for each Calendar Month in the year, similar to screenshot below. 

[Fowmy]

@Anonymous 

You need to provide more information about your model and how you want to visualize it. Do you have a calendar table?

Better, share a sample PBIX file.

[Anonymous]

Hi @Fowmy , 

You can get a sample pbix at the link below

https://www.dropbox.com/s/dvd12rvzoowxont/Sample.pbix?dl=0 

[Fowmy]

@Anonymous 

Please check my original reply, I modified the formula and attached file.

[Anonymous]

Hi @Fowmy ,

Thanks for that. 

How do I inlcude "if Cancellation or Expiry Date is blank"? I'm having some trouble with this. 

I modified the DAX to include if Cancellation Date or Expiry Date is blank, but it's not giving me the correct figure. 

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

[Fowmy · Accepted solution]

@Anonymous 

I modified them:

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

[Anonymous]

Hi @Fowmy , 

Thanks again. Your modified DAX works, but again I'm not getting the correct figures for Bookings or Revenue. 

The original SQL query returns around 4900 bookings for the month of April, whereas the DAX returns around 3100 bookings.

[Fowmy]

@Anonymous 

Better check both the tables for a particular month and find out which records not reconcilied 

[Anonymous]

Hi @Fowmy , 

Ignore my previous message! Your modified DAX works perfectly and is now giving me exactly what I was looking for. 

Thank you for your help. I will mark that post as the solution now. 

[Fowmy]

@Anonymous 

You are welcome 🙏 

I just started learning sql and this was a good one for me too. 

Thanks