cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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)
 
 
11 REPLIES 11
Highlighted
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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,

Highlighted

Mohd_Naim_0-1601392212197.png

Getting same error even with your solution.

 

Highlighted
Super User II
Super User II

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

1.PNG

what's the expected output that you want?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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

@Mohd_Naim 

maybe you can try this

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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

Highlighted

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

manikumar34_0-1601395157678.png

 

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. 

manikumar34_1-1601395234228.png

 

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

Highlighted

@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

1.PNG

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors