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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

SQL Query to DAX

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!

 

Kartiklal03_0-1626259797805.png

 

1 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"}
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

11 REPLIES 11
Fowmy
Super User
Super User

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

 

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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. 

 

Kartiklal03_0-1626261978930.png

 

@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.


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Hi @Fowmy , 

 

You can get a sample pbix at the link below

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

 

 

@Anonymous 

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


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

 

 

Kartiklal03_0-1626271211459.png

 

@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"}
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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.

@Anonymous 

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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. 

@Anonymous 

 

You are welcome 🙏 

 

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

Thanks 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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