END OF MONTH - what am i doing wrong

[GJD23]

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

[Anonymous]

Hi @GJD23 

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.

Best Regards,

Jayleny

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

[Greg_Deckler]

@GJD23 I would use EOM instead of ENDOFMONTH. And I would avoid TI functions entirely. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, 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-TIT...