cancel
Showing results for
Did you mean:
Helper I

Hello guys,

I'm making an error using DATEADD function but I don't know how to fix / how to do it in a different way.

Actually I have this formula to get the start date I need, and it works, but if I put it into a table it is affected by the OrderID, so for each line i get a different TESTDATE:

``````TESTDATE =
VAR LAST_DATE = ENDOFMONTH(Orders[OrderDate])
RETURN

I would like to get a fixed value, based not on the date of each order but simply based on my last order (so last date available in my colums.

Should be something like this, but it doesn't work:

``````TESTDATE =
VAR LAST_DATE = CALCULATE(ENDOFMONTH(Orders[OrderDate]); ALL(ID))
RETURN

Do you have an idea to solve it?

1 ACCEPTED SOLUTION
Super User

@pasno , eomonth and dateadd work with continuous date. Try like this example

TESTDATE =
VAR LAST_DATE = ENDOFMONTH(Orders[OrderDate])
RETURN
Eomonth(date(year(LAST_DATE);month(LAST_DATE) -1; day(LAST_DATE)),-1)+1

or

TESTDATE =
VAR LAST_DATE = CALCULATE(ENDOFMONTH(Orders[OrderDate]); ALL(ID))
RETURN
Eomonth(date(year(LAST_DATE);month(LAST_DATE) -1; day(LAST_DATE)),-1)+1

2 REPLIES 2
Super User

@pasno , eomonth and dateadd work with continuous date. Try like this example

TESTDATE =
VAR LAST_DATE = ENDOFMONTH(Orders[OrderDate])
RETURN
Eomonth(date(year(LAST_DATE);month(LAST_DATE) -1; day(LAST_DATE)),-1)+1

or

TESTDATE =
VAR LAST_DATE = CALCULATE(ENDOFMONTH(Orders[OrderDate]); ALL(ID))
RETURN
Eomonth(date(year(LAST_DATE);month(LAST_DATE) -1; day(LAST_DATE)),-1)+1

Helper I

Nice workaround, it works!

Thank you very much!