Forum Discussion

Tezaroyal's avatar
Tezaroyal
Frequent Visitor
3 years ago
Solved

Dax issue for In between the date range count

Hi,

 

I wrote below dax query for bookings count for selected date range if the booking (Created and cancelled date)is between the date range it should be 0 and my query working.but in my data there are some duplicates records with different properties for example below is screenshot of two records with same number still both stays are different so we need show both, as my selected date range is 1-1-2021 to 01-07-2023 the record No show should be 0 but its showing 1 and you can see in another screenshot  for other records which doesnt contain duplicates the logic is working fine, is this because of duplicates or anything else please help me

 

                            

Bookings Logic =
VAR __mindate = MIN('Calendar'[Date])
VAR __maxdate = MAX('Calendar'[Date])

 

var a = sumX('report vwRevenueDashboardReport_BookingCard',IF('report vwRevenueDashboardReport_BookingCard'[Createddate]<__mindate&&'report vwRevenueDashboardReport_BookingCard'[Canceldate]>=__mindate&&'report vwRevenueDashboardReport_BookingCard'[Canceldate]<=__maxdate,-1,
IF(('report vwRevenueDashboardReport_BookingCard'[Canceldate]<__mindate&&'report vwRevenueDashboardReport_BookingCard'[Createddate]<__mindate)||('report vwRevenueDashboardReport_BookingCard'[Createddate]<__mindate&&'report vwRevenueDashboardReport_BookingCard'[Canceldate]>__maxdate)||('report vwRevenueDashboardReport_BookingCard'[Canceldate]>__maxdate&&'report vwRevenueDashboardReport_BookingCard'[Createddate]>__maxdate)||('report vwRevenueDashboardReport_BookingCard'[Createddate]>__maxdate&&'report vwRevenueDashboardReport_BookingCard'[Canceldate]=BLANK()),BLANK(),
if(('report vwRevenueDashboardReport_BookingCard'[Createddate]>=__mindate&&'report vwRevenueDashboardReport_BookingCard'[Canceldate]>__maxdate)||('report vwRevenueDashboardReport_BookingCard'[Createddate]>=__mindate&&'report vwRevenueDashboardReport_BookingCard'[Canceldate]=BLANK()),1,
if('report vwRevenueDashboardReport_BookingCard'[Createddate]>=__mindate&&'report vwRevenueDashboardReport_BookingCard'[Canceldate]<=__maxdate,0,BLANK())))))
return a
   

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Tezaroyal ,

     

    I suggest you to try code as below.

    Bookings Logic =
VAR _ADD =
    ADDCOLUMNS (
        'report vwRevenueDashboardReport_BookingCard',
        "Bookings Logic1",
            VAR __mindate =
                MIN ( 'Calendar'[Date] )
            VAR __maxdate =
                MAX ( 'Calendar'[Date] )
            VAR a =
                SUMX (
                    'report vwRevenueDashboardReport_BookingCard',
                    IF (
                        'report vwRevenueDashboardReport_BookingCard'[Createddate] < __mindate
                            && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] >= __mindate
                            && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] <= __maxdate,
                        -1,
                        IF (
                            ( 'report vwRevenueDashboardReport_BookingCard'[Canceldate] < __mindate
                                && 'report vwRevenueDashboardReport_BookingCard'[Createddate] < __mindate )
                                || ( 'report vwRevenueDashboardReport_BookingCard'[Createddate] < __mindate
                                && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] > __maxdate )
                                || ( 'report vwRevenueDashboardReport_BookingCard'[Canceldate] > __maxdate
                                && 'report vwRevenueDashboardReport_BookingCard'[Createddate] > __maxdate )
                                || (
                                    'report vwRevenueDashboardReport_BookingCard'[Createddate] > __maxdate
                                        && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] = BLANK ()
                                ),
                            BLANK (),
                            IF (
                                ( 'report vwRevenueDashboardReport_BookingCard'[Createddate] >= __mindate
                                    && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] > __maxdate )
                                    || (
                                        'report vwRevenueDashboardReport_BookingCard'[Createddate] >= __mindate
                                            && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] = BLANK ()
                                    ),
                                1,
                                IF (
                                    'report vwRevenueDashboardReport_BookingCard'[Createddate] >= __mindate
                                        && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] <= __maxdate,
                                    0,
                                    BLANK ()
                                )
                            )
                        )
                    )
                )
            RETURN
                a
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD,
        "Bookings Logic2",
            VAR _LIST =
                SUMMARIZE (
                    FILTER ( _ADD, [RevenueNumber] = EARLIER ( [RevenueNumber] ) ),
                    [Bookings Logic1]
                )
            RETURN
                IF ( 0 IN _LIST, 0, [Bookings Logic1] )
    )
RETURN
    SUMX ( _ADD2, [Bookings Logic2] )

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Tezaroyal ,

     

    I suggest you to try code as below.

    Bookings Logic =
VAR _ADD =
    ADDCOLUMNS (
        'report vwRevenueDashboardReport_BookingCard',
        "Bookings Logic1",
            VAR __mindate =
                MIN ( 'Calendar'[Date] )
            VAR __maxdate =
                MAX ( 'Calendar'[Date] )
            VAR a =
                SUMX (
                    'report vwRevenueDashboardReport_BookingCard',
                    IF (
                        'report vwRevenueDashboardReport_BookingCard'[Createddate] < __mindate
                            && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] >= __mindate
                            && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] <= __maxdate,
                        -1,
                        IF (
                            ( 'report vwRevenueDashboardReport_BookingCard'[Canceldate] < __mindate
                                && 'report vwRevenueDashboardReport_BookingCard'[Createddate] < __mindate )
                                || ( 'report vwRevenueDashboardReport_BookingCard'[Createddate] < __mindate
                                && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] > __maxdate )
                                || ( 'report vwRevenueDashboardReport_BookingCard'[Canceldate] > __maxdate
                                && 'report vwRevenueDashboardReport_BookingCard'[Createddate] > __maxdate )
                                || (
                                    'report vwRevenueDashboardReport_BookingCard'[Createddate] > __maxdate
                                        && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] = BLANK ()
                                ),
                            BLANK (),
                            IF (
                                ( 'report vwRevenueDashboardReport_BookingCard'[Createddate] >= __mindate
                                    && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] > __maxdate )
                                    || (
                                        'report vwRevenueDashboardReport_BookingCard'[Createddate] >= __mindate
                                            && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] = BLANK ()
                                    ),
                                1,
                                IF (
                                    'report vwRevenueDashboardReport_BookingCard'[Createddate] >= __mindate
                                        && 'report vwRevenueDashboardReport_BookingCard'[Canceldate] <= __maxdate,
                                    0,
                                    BLANK ()
                                )
                            )
                        )
                    )
                )
            RETURN
                a
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD,
        "Bookings Logic2",
            VAR _LIST =
                SUMMARIZE (
                    FILTER ( _ADD, [RevenueNumber] = EARLIER ( [RevenueNumber] ) ),
                    [Bookings Logic1]
                )
            RETURN
                IF ( 0 IN _LIST, 0, [Bookings Logic1] )
    )
RETURN
    SUMX ( _ADD2, [Bookings Logic2] )

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.