Forum Discussion

Tezaroyal's avatar
Tezaroyal
Frequent Visitor
3 years ago
Solved

Slicer issue

Hi , I have created the slicer with a date table  to filter the count of nights between Arrivaldate and Departure dates, which is on the bookings table. For example, The date range that we selecte...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Tezaroyal ,

    Please try:

     

    TotalNights = 
VAR startdate =
    MIN ( 'Calendar'[Date] )
VAR enddate =
    MAX ( 'Calendar'[Date] )
VAR ArrivalDate =
    SELECTEDVALUE ( 'report vwRevenueDashboardReport_Data'[ArrivalDate] )
VAR DepartureDate =
    SELECTEDVALUE ( 'report vwRevenueDashboardReport_Data'[DepartureDate] )
VAR AdjustedArrivalDate =
    IF ( ArrivalDate < startdate, startdate, ArrivalDate )
VAR AdjustedDepartureDate =
    IF ( DepartureDate > enddate, enddate, DepartureDate )
VAR Result =
    SWITCH (
        TRUE (),
        AdjustedArrivalDate <= AdjustedDepartureDate
            && DepartureDate > enddate, DATEDIFF ( AdjustedArrivalDate, AdjustedDepartureDate, DAY ) + 1,
        AdjustedArrivalDate <= AdjustedDepartureDate, DATEDIFF ( AdjustedArrivalDate, AdjustedDepartureDate, DAY ),
        0
    )
RETURN
    Result

     

    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

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Tezaroyal ,

     

    Please try:

    TotalNights = 
VAR startdate =
    MIN ( 'Calendar'[Date] )
VAR enddate =
    MAX ( 'Calendar'[Date] )
VAR ArrivalDate =
    SELECTEDVALUE ( 'report vwRevenueDashboardReport_Data'[ArrivalDate] )
VAR DepartureDate =
    SELECTEDVALUE ( 'report vwRevenueDashboardReport_Data'[DepartureDate] )
VAR AdjustedArrivalDate =
    IF ( ArrivalDate < startdate, startdate, ArrivalDate )
VAR AdjustedDepartureDate =
    IF ( DepartureDate > enddate, enddate, DepartureDate )
VAR Result =
    SWITCH (
        TRUE (),
        AdjustedArrivalDate <= AdjustedDepartureDate
            && DepartureDate > enddate, DATEDIFF ( AdjustedArrivalDate, AdjustedDepartureDate, DAY ) + 1,
        AdjustedArrivalDate <= AdjustedDepartureDate, DATEDIFF ( AdjustedArrivalDate, AdjustedDepartureDate, DAY )
    )
RETURN
    Result
    Measure = 
SUMX(
    'report vwRevenueDashboardReport_Data',
    [TotalNights]
)

     

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Tezaroyal ,

Please try:

 

TotalNights = 
VAR startdate =
    MIN ( 'Calendar'[Date] )
VAR enddate =
    MAX ( 'Calendar'[Date] )
VAR ArrivalDate =
    SELECTEDVALUE ( 'report vwRevenueDashboardReport_Data'[ArrivalDate] )
VAR DepartureDate =
    SELECTEDVALUE ( 'report vwRevenueDashboardReport_Data'[DepartureDate] )
VAR AdjustedArrivalDate =
    IF ( ArrivalDate < startdate, startdate, ArrivalDate )
VAR AdjustedDepartureDate =
    IF ( DepartureDate > enddate, enddate, DepartureDate )
VAR Result =
    SWITCH (
        TRUE (),
        AdjustedArrivalDate <= AdjustedDepartureDate
            && DepartureDate > enddate, DATEDIFF ( AdjustedArrivalDate, AdjustedDepartureDate, DAY ) + 1,
        AdjustedArrivalDate <= AdjustedDepartureDate, DATEDIFF ( AdjustedArrivalDate, AdjustedDepartureDate, DAY ),
        0
    )
RETURN
    Result

 

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

  • Tezaroyal's avatar
    Tezaroyal
    Frequent Visitor
    3 years ago

    Thanks for the solution Anonymous , I am able to see the count for beyond slicer ranges. But in my table visible, it is populating all dates records like from begining, I understand that our formula doesnt have control over filtering based on dates. but I need to populate the records on table visual which are at least one active night based on slicer dates, for example my table visual should contain the records of either arrival or departure dates should fall in dates range and exclude all previous or after dates. 

     

    Example:   

    date slicer range : 07/01/2021 - 07/15/2023

     

    in table visual, I want to populate only the records which are active in this range only like below

     

    arrival date    departure date  total nights

    6/25/2023       7/5/2023         4

    7/10/2023       7/14/2023       4

    7/10/2023        7/25/2023      6

     

    but should not populate the records like below

    6/25/2023     6/29/2023   4

    7/16/2023     7/20/2023   4

     

    also I am not able to see the grand total for that columns. if possible please help on this also. Thank you so much for your help.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Tezaroyal ,

       

      Please try:

      TotalNights = 
VAR startdate =
    MIN ( 'Calendar'[Date] )
VAR enddate =
    MAX ( 'Calendar'[Date] )
VAR ArrivalDate =
    SELECTEDVALUE ( 'report vwRevenueDashboardReport_Data'[ArrivalDate] )
VAR DepartureDate =
    SELECTEDVALUE ( 'report vwRevenueDashboardReport_Data'[DepartureDate] )
VAR AdjustedArrivalDate =
    IF ( ArrivalDate < startdate, startdate, ArrivalDate )
VAR AdjustedDepartureDate =
    IF ( DepartureDate > enddate, enddate, DepartureDate )
VAR Result =
    SWITCH (
        TRUE (),
        AdjustedArrivalDate <= AdjustedDepartureDate
            && DepartureDate > enddate, DATEDIFF ( AdjustedArrivalDate, AdjustedDepartureDate, DAY ) + 1,
        AdjustedArrivalDate <= AdjustedDepartureDate, DATEDIFF ( AdjustedArrivalDate, AdjustedDepartureDate, DAY )
    )
RETURN
    Result
      Measure = 
SUMX(
    'report vwRevenueDashboardReport_Data',
    [TotalNights]
)

       

      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

      • Tezaroyal's avatar
        Tezaroyal
        Frequent Visitor
        3 years ago

        Thank you so much🤝Anonymous , Now its working perfectly as i expected once again thanks a lot😊