Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Need help with Day Count measure please

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 Curren...
Need Help
  • v-hashadapu's avatar
    v-hashadapu
    1 year ago

    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's avatar
DataNinja777
Icon for Super User rankSuper User
1 year ago

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,