Slicer issue

[Tezaroyal]

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()

)))))))

[v-cgao-msft · Accepted solution]

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

[v-cgao-msft · Accepted solution]

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]

Thanks for the solution @v-cgao-msft , 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.

[v-cgao-msft · Accepted solution]

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]

Thank you so much🤝@v-cgao-msft , Now its working perfectly as i expected once again thanks a lot😊