Time intelligence function returning wrong month

[Junkay]

Hi All,

I'm tring to extract month from date column in the table shown below. But I'm getting wrong month. For the month of July, Jan is returned in the month column. Please what could be the issue? I have checked my formula over and over again, but don't see anything wrong.

[OwenAuger · Accepted solution]

Hi @Junkay 

Change line 3 of the code to

"Month", FORMAT ( [DATE], "mmm" ),

[OwenAuger · Accepted solution]

Hi again @Junkay 

The ENDOFMONTH function will produce the expected result as long as the column reference provided as an argument is for a column which contains complete calendar months. If any dates are missing, this function treats the last available date in a given calendar month as the last day of the month. The tutorial you were using may have already had a table containing complete months.

For this reason, I would generally not advise using ENDOFMONTH to return a scalar end-of-month value. EOMONTH is the best function for this.

ENDOFMONTH is typically used within CALCULATE to modify a date filter by shifting it to the last day of the last visible month. ENDOFMONTH returns a 1x1 table which retains lineage to the column reference passed as its argument.

On to your latest question, to return an end-of-quarter date, I would advise using EOMONTH again.

For standard calendar quarters (Jan-Mar, Apr-Jun etc), an expression like this should work:

EOMONTH ( [Date], MOD ( - MONTH ( [Date] ), 3 ) )

[Junkay]

Thanks greatly Owen,

EOMONTH function worked. But I wonder why ENDOFMONTH get accurate result in the tutorial I was using, but not working for me. Also, I have same challenge with ENDOF QUARTER function. It’s not returning the last day of the quarter. I’m not sure there’s EOQUARTER function in Power BI.

Formula:

Result:

Thanks Owen.

[OwenAuger · Accepted solution]

Hi again @Junkay 

The ENDOFMONTH function will produce the expected result as long as the column reference provided as an argument is for a column which contains complete calendar months. If any dates are missing, this function treats the last available date in a given calendar month as the last day of the month. The tutorial you were using may have already had a table containing complete months.

For this reason, I would generally not advise using ENDOFMONTH to return a scalar end-of-month value. EOMONTH is the best function for this.

ENDOFMONTH is typically used within CALCULATE to modify a date filter by shifting it to the last day of the last visible month. ENDOFMONTH returns a 1x1 table which retains lineage to the column reference passed as its argument.

On to your latest question, to return an end-of-quarter date, I would advise using EOMONTH again.

For standard calendar quarters (Jan-Mar, Apr-Jun etc), an expression like this should work:

EOMONTH ( [Date], MOD ( - MONTH ( [Date] ), 3 ) )

[Junkay · Accepted solution]

Hi Owen,

You are a genius. Thank you so much for the support.

I wish I could be this proficient.

Thanks a million times.

[Junkay]

Thank you so much, Owen. This resolved it.

But I have another challenge. My End of Month function is also returning wrong result:

Formular below:

Result below:

Thanks for your support.

[OwenAuger]

@Junkay Glad to hear it 🙂

If you want to return the end-of-month date as a scalar value, you should use the EOMONTH function (which works the same as in Excel).

In your example:

EOMONTH ( Orders[Order Date], 0 )

ENDOFMONTH is a time intelligence function, normally used to modify date filters. It will return a single-column/single-row table containing the last date of a month from the set of dates that exist in the column referenced.

Regards

[OwenAuger · Accepted solution]

Hi @Junkay 

Change line 3 of the code to

"Month", FORMAT ( [DATE], "mmm" ),