Need help with Day Count measure please

[RichOB]

Hi, I need to count the amount of days a dog is in a kennel. Specifically, I need help with a measure that sets TODAY as the end date for when either there is no End_Date or when the Status is Current. The end goal is to get a total count of the historic and current combined into a table:

Kennel	Dog	Start_Date	End_Date	Status	Day_Count
1	Max	24/02/2024	04/03/2024	Historic	10
1	Charles	04/03/2024	25/07/2024	Historic	144
1	Lila	25/07/2024		Current	369
2	Milo	01/06/2024	26/08/2024	Historic	87
3	Rex	08/09/2024	10/10/2024	Historic	33
4	Birdie	01/11/2024	30/12/2024	Historic	61
4	Bob	30/12/2024		Current	144
					848

Thanks

Rich

[v-hashadapu]

Hi @RichOB ,
I hope the information shared was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you!

[v-hashadapu]

Hi @RichOB ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!

[v-hashadapu]

Hi @RichOB , Thank you for reaching out to the Microsoft Community Forum.

Please try below (I assumed kennel as table name):

Day_Count =

SUMX(

    'Kennel',

    VAR StartDate = 'Kennel'[Start_Date]

    VAR EndDateRaw =

        IF(

            ISBLANK('Kennel'[End_Date]) || 'Kennel'[Status] = "Current",

            TODAY(),

            'Kennel'[End_Date]

        )

    VAR EndDate = MIN(EndDateRaw, TODAY())  -- Prevent future dates

    VAR DayCount =

        IF(

            NOT ISBLANK(StartDate) && NOT ISBLANK(EndDate) && StartDate <= EndDate,

            DATEDIFF(StartDate, EndDate, DAY) + 1,

            0

        )

    RETURN

        DayCount

)

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[DataNinja777]

Hi @RichOB ,

You can create a DAX measure that calculates the number of days a dog is in a kennel by using DATEDIFF to find the difference between the start date and either the end date or today’s date if the end date is blank and the status is "Current". Use SUMX to iterate over the table and sum the day counts across all rows.

Day_Count = 
SUMX (
    'KennelTable',
    DATEDIFF(
        'KennelTable'[Start_Date],
        IF (
            NOT ISBLANK('KennelTable'[End_Date]),
            'KennelTable'[End_Date],
            IF (
                'KennelTable'[Status] = "Current",
                TODAY(),
                'KennelTable'[End_Date]
            )
        ),
        DAY
    )
)

This measure assumes that both Start_Date and End_Date are properly formatted as date fields. If you're seeing incorrect values, check that there are no time components or text format issues in the date fields.

Best regards,

[johnt75]

Try

Day Count =
SUMX (
    'Table',
    VAR EndDate =
        IF (
            ISBLANK ( 'Table'[End Date] )
                || 'Table'[Status] = "Current",
            TODAY (),
            'Table'[End Date]
        )
    VAR Result = EndDate - 'Table'[Start Date]
    RETURN
        Result
)