topic Re: Calculate Days in Two Months Based on Start and End Date in DAX Commands and Tips

Calculate Days in Two Months Based on Start and End Date

dvraan — Tue, 17 Sep 2024 21:21:28 GMT

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

Re: Calculate Days in Two Months Based on Start and End Date

lbendlin — Tue, 17 Sep 2024 23:22:00 GMT

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 ) )

Re: Calculate Days in Two Months Based on Start and End Date

Anonymous — Wed, 18 Sep 2024 05:54:23 GMT

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.

Re: Calculate Days in Two Months Based on Start and End Date

dvraan — Thu, 19 Sep 2024 00:08:23 GMT

Thanks Anonymous and lbendlin both answers were helpful. Thanks again