Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DATEADD BUG

Hello Everyone.

 

I have this formula here that gives me sales for the same period last month.  IE: if today is June 8th it will show sales up to May 8th.

 

Sales -1M (Same Period) =
CALCULATE([Sales],
     DATEADD(  FILTER(   DATESMTD('(Dim) Calendar'[Date]),  '(Dim) Calendar'[Date]<TODAY() ), -1,   MONTH)
)
 
Unfortunatley this will only give sales values up until Same period -1 Day.  EI: if Its June 8th will show sales up till May 7th.
 
I want it to give sales up till May 8th. If today Is June 8th.
 
So if I thought if I change < TODAY to <= TODAY it would work, but instead it will give ALL of May.
 
This seems to be a bug to me.
 
Does anyone know how to fix it so it works?
 
Thank you.
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , Try one of the two options

     

    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

     

    Last MTD QTY forced=
    var _max = date(year(today()),month(today())-1,day(today()))
    return
    CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Try one of the two options

     

    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

     

    Last MTD QTY forced=
    var _max = date(year(today()),month(today())-1,day(today()))
    return
    CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you very much the second one worked.

       

      Sales -1M (Same Period) =
      VAR MAXDATE = DATE(YEAR(today()),MONTH(TODAY())-1,DAY(TODAY()))
      RETURN
      CALCULATE([Sales],DATESMTD(DATEADD('(Dim) Calendar'[Date],-1,MONTH)),'(Dim) Calendar'[Date] <= MAXDATE)
       
      Do you know why the first formula didnt work?  is it a Bug with DATEADD or TODAY, when using <=?