Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DATEADD Result return blank (Please help)

Hi All

 

Im currently having an error with DATEADD Dax function, in the middle of the data there is a blank result which I couldnt figure out why.

 

Dax used:

Measure 1 = DATEADD(Sheet1[CAL_CLOSING_MONTH],-2,month)
Measure 2 = VAR A=Sheet1[Measure 1] RETURN CALCULATE([Count CM],FILTER(ALL(Sheet1),Sheet1[CAL_CLOSING_MONTH]=A))
 
After using these measures the other values are correct, but Sep'21 data is missing. 
 

 

Please advice. Thank you.

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    Dateadd: Returns a table that contains a column of dates

    Please try to use EOMONTH.

     

    Measure 1 = EOMONTH(MAX(Sheet1[Month]),-2)
    Measure =
VAR A = Sheet1[Measure 1]
RETURN
    CALCULATE (
        MAX ( Sheet1[Count CM] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Month] = A )
    )

     

     

    Best Regards

    Community Support Team _ Polly

     

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

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You should always use date for that. dateadd need continuous dates

     

    Measure 1 = DATEADD(Date[Date],-2,month)

     

    example

     

    Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Dateadd: Returns a table that contains a column of dates

    Please try to use EOMONTH.

     

    Measure 1 = EOMONTH(MAX(Sheet1[Month]),-2)
    Measure =
VAR A = Sheet1[Measure 1]
RETURN
    CALCULATE (
        MAX ( Sheet1[Count CM] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Month] = A )
    )

     

     

    Best Regards

    Community Support Team _ Polly

     

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