Calculate Days in Two Months Based on Start and End Date

[dvraan]

EVALUATE
VAR StartDate = DATE(2024, 9, 17)   
VAR EndDate = DATE(2024, 10, 5)    

-- Find the last day of the start month (September)
VAR EndOfStartMonth = EOMONTH(StartDate, 0)

-- Calculate the number of days in the start month (September)
VAR DaysInStartMonth = DATEDIFF(StartDate, EndOfStartMonth, DAY) + 1

-- Calculate the number of days in the end month (October)
VAR StartOfEndMonth = DATE(YEAR(EndDate), MONTH(EndDate), 1)
VAR DaysInEndMonth = DATEDIFF(StartOfEndMonth, EndDate, DAY) + 1

-- Return the result
RETURN
    UNION (
        ROW ( "Month",  FORMAT(MONTH(StartDate), "mmmm", "en-US"), "Days", DaysInStartMonth ),
        ROW ( "Month", FORMAT(MONTH(EndDate), "mmmm", "en-US"), "Days", DaysInEndMonth )
    )

 without using format function i get correct month like 09,10 as month. but when i pass month(startDate) to format function returns only January as month. Any why it does that? 

Month Days

January	14
January	5

I want to get

Month Days

September	14
October	5

[lbendlin · Accepted solution]

EVALUATE
VAR StartDate = DATE(2024, 9, 17)   
VAR EndDate = DATE(2024, 10, 5)    

-- Find the last day of the start month (September)
VAR EndOfStartMonth = EOMONTH(StartDate, 0)

-- Calculate the number of days in the start month (September)
VAR DaysInStartMonth = DATEDIFF(StartDate, EndOfStartMonth, DAY) + 1

-- Calculate the number of days in the end month (October)
VAR StartOfEndMonth = DATE(YEAR(EndDate), MONTH(EndDate), 1)
VAR DaysInEndMonth = DATEDIFF(StartOfEndMonth, EndDate, DAY) + 1

-- Return the result
RETURN
    UNION (
        ROW ( "Month",  FORMAT(StartDate, "mmmm", "en-US"), "Days", DaysInStartMonth ),
        ROW ( "Month", FORMAT(EndDate, "mmmm", "en-US"), "Days", DaysInEndMonth )
    )

[dvraan]

Thanks @v-kaiyue-msft and @lbendlin both answers were helpful. Thanks again

[v-kaiyue-msft · Accepted solution]

Hi @dvraan ,

When using FORMAT(MONTH(StartDate), “mmm”, “en-US”), it interprets MONTH(StartDate) as a number (e.g., 9 for September) and formatted as a date. To get the correct month name, you should format the entire date, not just the month number. You can refer to the program provided by lbendlin .

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[lbendlin · Accepted solution]

EVALUATE
VAR StartDate = DATE(2024, 9, 17)   
VAR EndDate = DATE(2024, 10, 5)    

-- Find the last day of the start month (September)
VAR EndOfStartMonth = EOMONTH(StartDate, 0)

-- Calculate the number of days in the start month (September)
VAR DaysInStartMonth = DATEDIFF(StartDate, EndOfStartMonth, DAY) + 1

-- Calculate the number of days in the end month (October)
VAR StartOfEndMonth = DATE(YEAR(EndDate), MONTH(EndDate), 1)
VAR DaysInEndMonth = DATEDIFF(StartOfEndMonth, EndDate, DAY) + 1

-- Return the result
RETURN
    UNION (
        ROW ( "Month",  FORMAT(StartDate, "mmmm", "en-US"), "Days", DaysInStartMonth ),
        ROW ( "Month", FORMAT(EndDate, "mmmm", "en-US"), "Days", DaysInEndMonth )
    )