DAX to add last day of month in blank End Date column

[RichOB]

Hi, using the dog kennel table below, I'm looking for a column/measure that when the End date is blank, it gives me the last date of the month that we are currently in, and puts the number count in Numbe_of_Days_Open, please. Thanks!

Location	Kennel	Start Date	End Date	Number of Days
Manchester	1	01/04/2024	10/04/2024	10
Manchester	1	01/06/2024	14/06/2024	14
Manchester	1	28/11/2024

[AnkitaaMishra · Accepted solution]

Hi @RichOB , 
Try below DAX to create a measure: 

Number_of_Days_Open_Measure_withcorrecttotal =

VAR CurrentDate = TODAY()

VAR EndOfMonthDate = EOMONTH(CurrentDate, 0)

VAR StartDate = MAX('Table'[Start Date])

VAR EndDate = MAX('Table'[End Date])

VAR DaysOpen =

    IF(

        ISBLANK(StartDate),

        BLANK(),

        IF(

            ISBLANK(EndDate),

            DATEDIFF(StartDate, EndOfMonthDate, DAY),

            DATEDIFF(StartDate, EndDate, DAY)

        )

    )

RETURN

    IF(

        HASONEVALUE('Table'[Start Date]),

        DaysOpen, 

        BLANK()  

    )

Thanks,

Ankita

[v-linyulu-msft · Accepted solution]

Thanks for the reply from AnkitaaMishra , please allow me to provide another insight:

Hi, @RichOB 

AnkitaaMishra 's solution is a good idea for measures.Now, I will provide a solution for calculated columns:

1.Firstly, you need to create the following two calculated columns:

end1 = 
IF ( ISBLANK ( 'Table'[End Date] ), EOMONTH ( TODAY (), 0 ), 'Table'[End Date] )

Number of Days1 = 
IF (
    ISBLANK ( 'Table'[End Date] ),
    DATEDIFF ( 'Table'[Start Date], EOMONTH ( TODAY (), 0 ), DAY ) + 1,
    DATEDIFF ( 'Table'[Start Date], 'Table'[End Date], DAY ) + 1
)

2.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnkitaaMishra · Accepted solution]

Hi @RichOB , 
Try below DAX to create a measure: 

Number_of_Days_Open_Measure_withcorrecttotal =

VAR CurrentDate = TODAY()

VAR EndOfMonthDate = EOMONTH(CurrentDate, 0)

VAR StartDate = MAX('Table'[Start Date])

VAR EndDate = MAX('Table'[End Date])

VAR DaysOpen =

    IF(

        ISBLANK(StartDate),

        BLANK(),

        IF(

            ISBLANK(EndDate),

            DATEDIFF(StartDate, EndOfMonthDate, DAY),

            DATEDIFF(StartDate, EndDate, DAY)

        )

    )

RETURN

    IF(

        HASONEVALUE('Table'[Start Date]),

        DaysOpen, 

        BLANK()  

    )

Thanks,

Ankita