I need a help with a day count measure

[RichOB]

Hi, I have 2 dog boarding kennels and want to know the day count between the previous Dog ending and new one starting for each room.

For example, the day difference between Dog1 and Dog2 in April 2024 A1 is 2 days.

I do not have the Days_to_Fill column in my source data.

Location	Room	Dog_Number	Start_Date	End_Date	Days_to_Fill
Manchester	A1	1	01/04/2024	10/04/2024	2
Manchester	A1	2	13/04/2024	18/04/2024	7
Manchester	A1	3	26/04/2024	28/04/2024	5
Manchester	A1	4	03/05/2024	15/05/2024
Newcastle	B1	5	01/07/2024	12/07/2024	4
Newcastle	B1	6	17/07/2024	23/07/2024	15
Newcastle	B1	7	08/08/2024	18/08/2024	2
Newcastle	B1	8	21/08/2024	25/08/2024

Thanks

[Kedar_Pande]

@RichOB 

DAX for a Calculated Column:

Days_to_Fill =
VAR CurrentStart = 'Table'[Start_Date]
VAR CurrentRoom = 'Table'[Room]
VAR CurrentLocation = 'Table'[Location]
VAR PreviousEnd =
MAXX(
FILTER(
'Table',
'Table'[Room] = CurrentRoom &&
'Table'[Location] = CurrentLocation &&
'Table'[End_Date] < CurrentStart
),
'Table'[End_Date]
)
RETURN
IF(
NOT ISBLANK(PreviousEnd),
DATEDIFF(PreviousEnd, CurrentStart, DAY),
BLANK()
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[danextian]

I'm pretty confused

Row 1: Apr 10 to 13 = 3 days

Row 2 : Apr 18 to Apr 26 = 8 days

These should be the differences in days

Location	Room	Dog_Number	Start_Date	End_Date	Days_to_Fill	difference
Manchester	A1	1	01/04/2024	10/04/2024	2	3
Manchester	A1	2	13/04/2024	18/04/2024	7	8
Manchester	A1	3	26/04/2024	28/04/2024	5	5
Manchester	A1	4	03/05/2024	15/05/2024
Newcastle	B1	5	01/07/2024	12/07/2024	4	5
Newcastle	B1	6	17/07/2024	23/07/2024	15	16
Newcastle	B1	7	08/08/2024	18/08/2024	2	3
Newcastle	B1	8	21/08/2024	25/08/2024

[RichOB]

Hi @danextian thanks for your reply. What Measure did you use to get that please?

Thanks

[danextian · Accepted solution]

It is a calculated column

Difference = 
VAR NextStart =
    CALCULATE (
        MIN ( 'Table'[Start_Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Room] = EARLIER ( 'Table'[Room] )
                && 'Table'[Start_Date] > EARLIER ( 'Table'[Start_Date] )
        )
    )
RETURN
    DATEDIFF ( 'Table'[End_Date], NextStart, DAY )

[RichOB]

This worked perfectly thank you!

[AnkitaaMishra]

Hi @RichOB , 
Please try below DAX : 

Days_to_Fill_Calculation =

VAR _CheckRoom = 'ExampleTable'[Room]

VAR _CheckLocation = 'ExampleTable'[Location]

VAR _CheckStartDate = 'ExampleTable'[Start_Date]

VAR _PrevEndDate =

    CALCULATE(

        MAX('ExampleTable'[End_Date]),

        FILTER(

            'ExampleTable',

            'ExampleTable'[Location] = _CheckLocation &&

            'ExampleTable'[Room] = _CheckRoom &&

            'ExampleTable'[End_Date] < _CheckStartDate

        )

    )

RETURN

IF(

    ISBLANK(_PrevEndDate),

    BLANK(),

    DATEDIFF(_PrevEndDate, _CheckStartDate, DAY) -1

)

Output : 

You can adjust the formula (with or without -1)

Thanks,
Ankita

[RichOB]

Hi @AnkitaaMishra thanks so much for your help. I'm having a DAX error of " Cannot convert value 'Manchester' of type Text to type True/False".  Do you know how I would solve this please?