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 selected from Slicer is (7/1/2023- 7/13/2023 ) coming from the date table (The relationship between the date table & the bookings table is the Arrivaldate column). Now Customer A arrived on 7/1/2023 and departed on 7/16/2023. We need to count only the nights which he is staying in between the daterange which we selected in slicer and but not the depature date as he is leaving that day and the total nights are = 13 which i implemented by the logic i wrote below(Measure), 

 

Slicer -(7/1/2023- 7/13/2023 )

Arrivaldate - DepartureDate  Nights

7/1/2023      7/16/2023        13 (Count of nights only as per date range in slicer)

7/1/2023       7/13/2023       12 (As he departured on 7/13/2023, it should not count as night)

 

Now I need the count of nights who also arrived before 7/1/2023, like 6/25/2023 and departure on 7/5/2023, which means his departure date is still in between the date range which we have given, so it should count as only 4, starting from 7/1 to 7/5

Slicer -(7/1/2023- 7/13/2023 )

Arrivaldate - DepartureDate   Nights

6/25/2023      7/05/2023         4    (As the slicer date is from 7/1 to 7/13 how we need to show only nights he stayed in the range.?)

 

Can anyone please help me with this?

 

startdate = MinX('Calendar',('Calendar'[Date].[Date]))
enddate = MAXX('Calendar',('Calendar'[Date].[Date]))
 
TotalNights= IF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])<[startdate]&&SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])>[enddate], DATEDIFF([startdate],[enddate],DAY)+1,
 
    IF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])>=[startdate]&& SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])> [enddate],DATEDIFF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate]),[enddate],DAY)+1,
    IF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])<[startdate]&&SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])< [enddate],DATEDIFF([startdate],SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate]),DAY),
 
    IF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])>[startdate]&&SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])<[enddate], DATEDIFF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate]),SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate]),DAY),
 
    IF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])<=[startdate]&&SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])<[enddate],DATEDIFF([startdate],SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate]),DAY),
    IF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])<=[startdate]&&SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])=[enddate],DATEDIFF([startdate],SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate]),DAY),
 
    if(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])>[startdate]&&SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])=[enddate],DATEDIFF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate]),SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate]),DAY),BLANK()
)))))))

 

 

 

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

4 Replies

  • 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