Forum Discussion

Tezaroyal's avatar
Tezaroyal
Frequent Visitor
3 years ago
Solved

Dax Issue

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
 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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. 

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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. 

  • Tezaroyal's avatar
    Tezaroyal
    Frequent Visitor
    3 years ago

    Hi Anonymous  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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Nani
        Frequent Visitor
        3 years ago

        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