Dax issue for In between the date range count

[Tezaroyal]

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

[Anonymous · Accepted solution]

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.

[amitchandak]

@Tezaroyal , CHeck if formula for Current Employee in blog can help

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...