cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kartiklal03
Helper I
Helper I

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

@Kartiklal03 

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 IV
Super User IV

@Kartiklal03 

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

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

 

@Kartiklal03 

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

Hi @Fowmy , 

 

You can get a sample pbix at the link below

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

 

 

@Kartiklal03 

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

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

 

@Kartiklal03 

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

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.

@Kartiklal03 

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

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. 

@Kartiklal03 

 

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors