Forum Discussion

pasno's avatar
pasno
Helper II
4 years ago
Solved

Help with Date DATEADD

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
STARTOFMONTH(DATEADD(LAST_DATE; -1; MONTH))

 

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
STARTOFMONTH(DATEADD(LAST_DATE; -1; MONTH))

 

Do you have an idea to solve it?

 

Thank you in advance!

  • 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

  • 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

    • pasno's avatar
      pasno
      Helper II

      Nice workaround, it works!

       

      Thank you very much!