Dax Issue

[Tezaroyal]

Hi, 

Below is a measure for bookings between the created date column and the cancelled date column. I need to show the records that fall in the date range which i selected the slicer if any record  (created or cancel date) records that falls in the date range should show based on the slicer dates, as the below measure I'm getting all the created dates before the date range i selected 

Ex-6/19/2023 & 7/30/2023 are not in my range either (created date or cancel date) but the records are still showing. Can anyone please help me to eliminate these records by modifying the query?

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

[Tezaroyal]

Hi @v-tangjie-msft  Thanks for the logic its working fine now, But when I wrote another measure for count of bookings (Bookings Count = SUMX('report vwRevenueDashboardReport_BookingCard',[Bookings logic]) its not showing accurate count😥, I would be grateful if you can help me on this, please

[v-tangjie-msft]

Hi @Tezaroyal ,

Based on your screenshot, -1-1+0+0+0+0+1+1+1+1+1+1+1+1=3, and the TOTAL showing 3 is correct. Can you provide your expected results and detailed logic?

Best Regards,

Neeko Tang

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

[Nani]

Hi Sir, the logic and count is working fine, but some records are still under the date range that are not showing on the table.After selecting the show items with no data, the records which are missing are populated on the table along with all the records which are not in the date range. I'm totally confused 

[v-tangjie-msft · Accepted solution]

Hi @Nani ,

If you select "show items with no data", then you need to place the measure booking on the visual object filter.

Or you can deselect "show items with no data".

Best Regards,

Neeko Tang

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

[v-tangjie-msft]

Hi @Tezaroyal ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can update the measure. 

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
IF(MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate]) in VALUES('Calendar'[Date]) || MAX('report vwRevenueDashboardReport_BookingCard'[Createddate]) in VALUES('Calendar'[Date]) ,__result ,BLANK())

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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