END OF MONTH - what am i doing wrong

Question

Test Period end = ENDOFMONTH('AOP DATA'[Mid Mth Date])

I have a data table that has a mid month date calculated within the data - always 15th of the month. I also have a calendar table

In trying to calculate Aggregate yearly sales i have tried to use above formula to cretae an end Period for the agreate sales.

However when i check the result of the formula it is still returning the 15th of the month - I must bed doing something basic in correctly!!!

My full formula is

AOP Aggregate Sales =

VAR PeriodStart = STARTOFYEAR('AOP DATA'[Mid Mth Date])

VAR PeriodEnd = ENDOFMONTH('AOP DATA'[Mid Mth Date])

VAR AggSales = CALCULATE(SUM('AOP DATA'[Value]), 'Calendar'[Date] >= PeriodStart &&'Calendar'[Date] <= PeriodEnd)

Return AggSales

which i thought waas working but on checking think the period end date is causing an issue

greg_deckler · Answer

GJD23&nbsp;I would use EOM instead of ENDOFMONTH. And I would avoid TI functions entirely.&nbsp;You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

anonymous · Answer

Hi&nbsp;GJD23&nbsp;You might be having issues with the use of time intelligence functions because DAX time intelligence functions usually need to be associated with the correct date table. You directly used the Mid Mth Date column from the data table, which could cause the function to fail to recognize the end-of-month date.
&nbsp;
&nbsp;
&nbsp;
&nbsp;
Best Regards,
Jayleny
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

END OF MONTH - what am i doing wrong

2 Replies

Related content

Months in wrong order

Measure Killer not working as expected. What I'm doing wrong?

Power BI doing wrong calculations

FORMAT() returns wrong month

Month to Month Flow Calculation