Help with days in month DAX

[amymalone]

Hello,

Apologies, I am new to DAX and writing queries in general but I am trying to create a table for the occupied days within a month, taking into account an 'in date' and an 'out date'.

I have the calculation below, but it will only show me the bed days for the 'out date' and the total. It wont show me the days count for the previous months from the 'In Date'

Can anyone see where i am going wrong?

(i have a dates table as referenced below) 

Occupied Days  =

VAR InDate = VALUE( SELECTEDVALUE( 'Trial 2'[In Date] ) )

VAR OutDate= VALUE( SELECTEDVALUE( 'Trial 2'[Out Date] ) )

VAR MinDateInContext = VALUE( MIN( 'Date'[Date] ) )

VAR MaxDateInContext = VALUE( MAX( 'Date'[Date] ) )

RETURN

IF( AND( AdmitDate < MinDateInContext, OutDate> MinDateInContext ) ,

        MIN( DischargeDate, MaxDateInContext ) - MinDateInContext,

            IF( AND( AND(InDate>= MinDateInContext, InDate<= MaxDateInContext ), OutDate>= MinDateInContext ),

                 MIN( OutDate, MaxDateInContext ) - InDate,

                    BLANK() ) )

[sjoerdvn]

My guess is that in your model you have an active relationship between 'Trial 2'[Out Date] and your date dimension. That would filter out anything with an "out date" not in the selected period. But since you haven't shared any information on the tables and relationships involved we can only guess.
Anyway, if that is the case you can either disable the relationship for this measure using CROSSFILTER or make all relationships between the 'Trial 2' table and the date dimension inactive.

[Mahesh0016]

@amymalone 

It looks like you're trying to calculate the number of occupied days within a given month, taking into account both the "In Date" and "Out Date" from your data. However, it seems there are some issues in your DAX calculation. I'll help you correct it.

Here's a revised version of your DAX code:

```DAX
Occupied Days =
VAR InDate = VALUE(SELECTEDVALUE('Trial 2'[In Date]))
VAR OutDate = VALUE(SELECTEDVALUE('Trial 2'[Out Date]))
VAR MinDateInContext = VALUE(MIN('Date'[Date]))
VAR MaxDateInContext = VALUE(MAX('Date'[Date]))

RETURN
IF(
OutDate >= MinDateInContext,
IF(
InDate <= MaxDateInContext,
IF(InDate >= MinDateInContext, MIN(OutDate, MaxDateInContext) - InDate + 1, MIN(OutDate, MaxDateInContext) - MinDateInContext + 1),
0
),
0
)
```

Here's a breakdown of the changes made:

1. I've added a `+1` to the calculations inside the `IF` statements to include both the start and end dates in the count of occupied days.

2. I've modified the logic to check if the `InDate` is within the context of the selected date range (`MinDateInContext` to `MaxDateInContext`) and then calculate the occupied days accordingly.

3. I've added checks to ensure that `InDate` and `OutDate` are within the date range (`MinDateInContext` to `MaxDateInContext`) before calculating occupied days.

This revised DAX code should give you the correct count of occupied days within the selected month, considering both the "In Date" and "Out Date" and taking into account any overlap with the previous or following months.

[amymalone]

Thank-you for your help with this. That makes sense, unfortunately, the problem is still arising in that it is only returning the occupied days for the month of discharge.