Dax Issue for the date range after and before the selected dates

[Nani]

Hi 

I have two columns with createddate and  calnceldate from which i need to get the count of bookings, but they should get the values in the table after and before the date of created and cancelled columns.

(Sum of created reservations minus sum of cancelled reservations.)

As of my measure, the daterange in the slicer is taking  the created date as the start date by default.

But we need to show the dates before the createddate as the canceldate record falls in the date range we selected

For example, the daterange in slicer is 8/1/2023-8/10/2023.

Date 8/1/2023-8/10/2023            

    Createddate Canceldate    Bookings

    06/25/2023    08/03/2023       -1 (we need to show the dates before the createddate as the canceldate record falls in the date range we selected)

    07/28/2023    08/05/2023       -1 (Same as above)

    08/01/2023    08/06/2023        0  ((Sum of created reservations minus sum of cancelled reservations.)

    08/01/2023    08/01/2023        0  (Sum of created reservations minus sum of cancelled reservations.)

    08/02/2023    08/05/2023        0 (Sum of created reservations minus sum of cancelled reservations.)

    08/03/2023    08/12/2023        1  (only to consider the created date as its in the range)

    08/04/2023    09/10/2023        1   (only to consider the created date as its in the range)

    08/05/2023                              1  (As it doesn't have a canceled date)

    08/06/2023                              1  

    08/07/2023                              1

    08/08/2023                              1

    08/09/2023                              1

Anyone can help on this please. 

[Anonymous]

Hi @Nani ,

Please create 2 measures:

Measure = 
VAR __mindate = MIN('Date'[Date])
VAR __maxdate = MAX('Date'[Date])
VAR __curdate = MAX('Table'[Createddate])
VAR __created = 
SWITCH(
    TRUE(),
    __curdate < __mindate,
    0,
    ISBLANK(MAX('Table'[Canceldate])),
    0,1
)
VAR __cancelled = 
SWITCH(
    TRUE(),
    MAX('Table'[Canceldate]) >= __mindate
        && MAX('Table'[Canceldate]) <= __maxdate,
    -1,
    ISBLANK(MAX('Table'[Canceldate]))
    ,1
)
VAR __result = __created + __cancelled
RETURN
__result

Bookings = SUMX('Table',[Measure])

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Nani]

Hi @Anonymous  Thanks for responding😊, I applied the measure you suggested im getting the result as expetected the dates before the createddate if the canceldate record falls in the date range we selected are showing in the table, but the problem its showing the before created dates which are not in the daterange and also  there is no realted canceldate,we should not show other date records other than date range we selected until unless the records contain before or after the created and cancel date, 6/5/2023,7/30/2023 which are not in our date range but still showing in the table, Please help me out with this logic.

Bookings =

VAR __mindate = MIN('Calendar'[Date])

VAR __maxdate = MAX('Calendar'[Date])

VAR __curdate = MAX('report vwRevenueDashboardReport_BookingCard'[Createddate])

VAR __created =

SWITCH(

    TRUE(),

    __curdate < __mindate,

    0,

    ISBLANK(MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate])),

    0,1

)

VAR __cancelled =

SWITCH(

    TRUE(),

    MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate]) >= __mindate

        && MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate]) <= __maxdate,

    -1,

    ISBLANK(MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate]))

    ,1

)

VAR __result = __created + __cancelled

RETURN

__result

(And showing huge sum for small dateranges as you can see the image)

[Anonymous · Accepted solution]

Hi @Nani ,

Please try:

Measure = 
VAR __mindate = MIN('Date'[Date])
VAR __maxdate = MAX('Date'[Date])
VAR __curdate = MAX('Table'[Createddate])
VAR __created = 
SWITCH(
    TRUE(),
    __curdate < __mindate && ISBLANK(MAX('Table'[Canceldate])),
    BLANK(),
    __curdate < __mindate || ISBLANK(MAX('Table'[Canceldate])),
    0,
    1
)
VAR __cancelled = 
SWITCH(
    TRUE(),
    __curdate < __mindate && ISBLANK(MAX('Table'[Canceldate])),
    BLANK(),
    MAX('Table'[Canceldate]) >= __mindate
        && MAX('Table'[Canceldate]) <= __maxdate,
    -1,
    ISBLANK(MAX('Table'[Canceldate]))
    ,1
)
VAR __result = __created + __cancelled
RETURN
__result

Bookings = SUMX('Table',[Measure])

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.