Issue with Date function

[Mohd_Naim]

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)

[ryan_mayu]

@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]

@ryan_mayu, I know it is working for other dates, I am expecting a result 2020/08/31 using mentioned DAX.

[ryan_mayu]

@Mohd_Naim 

maybe you can try this

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

[Mohd_Naim]

@ryan_mayu, Already tried and implemented in same way Thanks!! , My only point to bring this up is what's wrong with Date function to calculate Date on mentioned DAX. 

[ryan_mayu]

@Mohd_Naim 

As what I mentioned, you can't transfer value 0 to day parameter in the date function. please check the official doc.

https://docs.microsoft.com/en-us/dax/date-function-dax

[amitchandak]

@Mohd_Naim , I checked out. it only do not support 0. I able to subtract 31

Last_30_Days =

VAR MaxDate = date(2020,09,30)

Return Date(YEAR(MaxDate),MONTH(MaxDate),DAY(MaxDate)-31)

[amitchandak]

@Mohd_Naim , I logged an issue.

https://community.powerbi.com/t5/Issues/Date-function-does-not-with-0-Day-Work-with-both-Positive-an...

[manikumar34]

@Mohd_Naim , Hey, I obsereved it is not working beacause your day which is 29 and the number you are substracting are same. 

For your refernce I increased a day here and it is working. Check the below screenshot which you are using is not working as they are same. 

By this we can understand that we have substract either higher number are lesser number than our day. 

If you think this is helpful Accept the Solution and leave a like.

Regards, 

Manikumar

[amitchandak]

@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]

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,

[Mohd_Naim]

Getting same error even with your solution.