Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mohd_Naim
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
ryan_mayu
Super User
Super User

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




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

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




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

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




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

@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





If this helps, Appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




amitchandak
Super User
Super User

@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

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_0-1601392212197.png

Getting same error even with your solution.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.