Need help with Day Count measure between end dates and start dates

[RichOB]

Hi, apologies for posting similar to a previous post, but my issue remains. I have student accommodation and under each Property I need to add the Days_Empty column.

1 - Per each bedspace, I need the count of days between the End_Date and the Start_Date of the next tenancy.

2 - When there is no end date I need the Days_Empty to show as 0.

3 - Per Bedspce, when there is an End_Date but no one has next moved into the bedspace, I need the Days_Empty count to be from the End_Date entered to Today's date. When someone moves into the bedspace and there is a new start date, I need that difference to be the final Days_Empty count.

Property_Number	Bedspace_Number	Tenancy_Name	Start_Date	End_Date	Days_Empty
PROP-000017	BEDS-000100	T-003316	02/02/2025		0
PROP-000017	BEDS-000101	T-003317	04/07/2019	11/10/2019	0
PROP-000017	BEDS-000101	T-003336	11/10/2019	18/10/2019	0
PROP-000017	BEDS-000101	T-003337	18/10/2019	31/10/2019	0
PROP-000017	BEDS-000101	T-003338	31/10/2019	22/11/2019	4
PROP-000017	BEDS-000101	T-003404	27/11/2019	07/03/2020	0
PROP-000017	BEDS-000102	T-003318	22/07/2019	06/10/2020	0
PROP-000024	BEDS-000126	T-003034	04/03/2019	16/09/2019	0
PROP-000024	BEDS-000126	T-003164	16/09/2019	09/10/2019	0
PROP-000024	BEDS-000126	T-003170	09/10/2019	05/12/2019	13
PROP-000024	BEDS-000126	T-003527	23/12/2019	05/02/2020	0
PROP-000024	BEDS-000211	T-003035	03/02/2016	25/09/2019	0
PROP-000024	BEDS-000211	T-003054	25/09/2019	26/09/2019	2
PROP-000024	BEDS-000211	T-003055	29/09/2019	07/07/2021	0

Thanks in advance!

[v-dineshya · Accepted solution]

Hi @RichOB ,

Thanks for reaching out to the Microsoft fabric community forum. 

Create a Calculated Column
You can use the following DAX expression for your Days_Empty calculated column:

Days_Empty =
VAR CurrentBedspace = 'Tenancies'[Bedspace_Number]
VAR CurrentEndDate = 'Tenancies'[End_Date]
VAR CurrentStartDate = 'Tenancies'[Start_Date]

VAR NextStartDate =
CALCULATE(
MIN('Tenancies'[Start_Date]),
FILTER(
'Tenancies',
'Tenancies'[Bedspace_Number] = CurrentBedspace &&
'Tenancies'[Start_Date] > CurrentStartDate
)
)

RETURN

IF(
ISBLANK(CurrentEndDate),
0,

IF(
ISBLANK(NextStartDate),
DATEDIFF(CurrentEndDate, TODAY(), DAY),
MAX(0, DATEDIFF(CurrentEndDate, NextStartDate, DAY))
)
)

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @RichOB ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[v-dineshya]

Hi @RichOB ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[jdbuchanan71]

Did you add this as a calculated column on your table?  It is not meant to be a measure.

[jdbuchanan71]

@RichOB 

Give this a try.  You would add it as a calculated column.

The large results like 1838 are bacause there is no next start date so it is using today's date per your 3rd rule.

Days Empty = 
VAR _Start = 'Table'[Start_Date]
VAR _End = 'Table'[End_Date]
VAR _NextStart =
    COALESCE (
        CALCULATE (
            MIN ( 'Table'[Start_Date] ),
            ALLEXCEPT ( 'Table', 'Table'[Bedspace_Number], 'Table'[Property_Number] ),
            'Table'[Start_Date] > _Start
        ),
        TODAY ()
    )
VAR _Days = INT ( _NextStart - _End )
RETURN 
    IF ( ISBLANK ( 'Table'[End_Date] ), 0, _Days )

[RichOB]

Hi @jdbuchanan71 thanks so much for your help. I think this is almost exactly what I need but something is up with it. Sometimes bullet point 3 works, like in example BEDS-000061 where the days empty are correct at 0, and sometimes (e.g. Beds-000106) it's counting from the previous end date to today's date, even though the newer start date should override that and change it to 0.  Any idea why that would be? thanks