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.

v-hashadapu's avatar
v-hashadapu
Icon for Community Support rankCommunity Support
1 year ago

Hi RichOB , Just checking in—were you able to resolve the issue?
If one of the replies helped, please consider marking it as "Accept as Solution" and giving a 'Kudos'. Doing so can assist other community members in finding answers more quickly.
Thank you!