Next Available Date returning date of 31st Dec 1899

[villa1980]

Hi all,

 I was kindly given this DAX formula for an issue I have returning available date based on description/bay and start date.

 It works really well, unfortunately it returns a value stated above for certain Bays.
What I would like to do is instead of the date I would like it to say "Bay Booked Out for Week".
DAX is Below.

Thanks

Min_Date =

var _Centre = SELECTEDVALUE(Next_Available_Date[Centre Name])

var _Bay = SELECTEDVALUE(Next_Available_Date[BAYDESC])

var _Date = SELECTEDVALUE(Next_Available_Date[STARTDATE])

var _Minute = SELECTEDVALUE(Next_Available_Date[Available_Minutes])

var _MinimumDate = MINX(FILTER(ALLSELECTED(Next_Available_Date),Next_Available_Date[BAYDESC]=_Bay),Next_Available_Date[STARTDATE])

RETURN

IF(

    (

        _Minute=0&&_Date>_MinimumDate

    )&&

    (

        _MinimumDate&&

        (

            _Minute>1||

            _Minute<540

        )

    ),

    _MinimumDate,

    IF(

        CALCULATE(MAX(Next_Available_Date[Available_Minutes]),FILTER(Next_Available_Date,Next_Available_Date[BAYDESC]=_Bay&&Next_Available_Date[STARTDATE]=_MinimumDate))=0&&

        CALCULATE(MAX(Next_Available_Date[Available_Minutes]),FILTER(Next_Available_Date,Next_Available_Date[BAYDESC]=_Bay&&Next_Available_Date[STARTDATE]=_Date))=0,

        _Date+1,

        _MinimumDate

    ))

[anmolmalviya05 · Accepted solution]

Hi @villa1980 , Please check the below measure:

Min_Date =
VAR _Centre = SELECTEDVALUE(Next_Available_Date[Centre Name])
VAR _Bay = SELECTEDVALUE(Next_Available_Date[BAYDESC])
VAR _Date = SELECTEDVALUE(Next_Available_Date[STARTDATE])
VAR _Minute = SELECTEDVALUE(Next_Available_Date[Available_Minutes])
VAR _MinimumDate = MINX(FILTER(ALLSELECTED(Next_Available_Date), Next_Available_Date[BAYDESC] = _Bay), Next_Available_Date[STARTDATE])

RETURN
IF (
CALCULATE(
MAX(Next_Available_Date[Available_Minutes]),
FILTER(Next_Available_Date, Next_Available_Date[BAYDESC] = _Bay && Next_Available_Date[STARTDATE] = _MinimumDate)
) = 0 &&
CALCULATE(
MAX(Next_Available_Date[Available_Minutes]),
FILTER(Next_Available_Date, Next_Available_Date[BAYDESC] = _Bay && Next_Available_Date[STARTDATE] = _Date)
) = 0,
"Bay Booked Out for Week",
IF (
_Minute = 0 && _Date > _MinimumDate && (_Minute > 1 || _Minute < 540),
_MinimumDate,
IF (
_Minute = 0,
_Date + 1,
_MinimumDate
)
)
)

[Ritaf1983]

Hi @villa1980 

Please provide a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone.  

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...