Forum Discussion

Mohd_Naim's avatar
Mohd_Naim
Helper II
5 years ago

Issue with Date function

Hi, Can someone help me to understand why below DAX througing Error, when it is working fine for other dates.
 
Last_30_Days =
VAR MaxDate = "2020-09-29"
Return Date(YEAR(MaxDate),MONTH(MaxDate),DAY(MaxDate)-29)
 
 
Need Help

11 Replies

Replies have been turned off for this discussion
  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Mohd_Naim , Try like

     

    Last_30_Days =
    VAR MaxDate = date(2020,09,29)
    Return Date(YEAR(MaxDate),MONTH(MaxDate),DAY(MaxDate)-29)

     

    Use date function to create date

    • Mohd_Naim's avatar
      Mohd_Naim
      Helper II
      5 years ago

      Hi,

       

      This is the actual DAX which is failing for 29 date, Can you please check by running DAX by changing dates, 

       

      Last_30_Days =
      VAR MaxDate = MAX(Dimdate[Date])
      Return Date(YEAR(MaxDate),MONTH(MaxDate),DAY(MaxDate)-29)

       

      Thanks,

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    Mohd_Naim 

    i am guessing the day 29-29=0, then can't create a date with the day is 0.

    if changed to 2020-09-30, then it works.

    what's the expected output that you want?

    • Mohd_Naim's avatar
      Mohd_Naim
      Helper II
      5 years ago
      ryan_mayu, I know it is working for other dates, I am expecting a result 2020/08/31 using mentioned DAX.
      • ryan_mayu's avatar
        ryan_mayu
        Super User
        5 years ago

        Mohd_Naim 

        maybe you can try this

        Last_30_Days = 
VAR MaxDate = "2020-09-29"
Return FORMAT((MaxDate-29),"yyyy-mm-dd")