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

date creation

Good afternoon

 

I have two tables. In the one I split a particular date into a DAY , MONTH and YEAR. I need to use the MONTH and YEAR from this table and the DAY from another table in this way:  

 
Payment Date = date('Payment Summary'[Payment Year],'Payment Summary'[Payment MONTH use],'Payment Summary'[Payment DAY related])
 
However as I add data into the Excel datasource I get this error (although it mostly happens .. it sometimes doesn't) and I have no idea why. The error is:  An argument of function 'DATE' has the wrong data type or the result is too large or too small
 
I have no idea how to resolve this. Is there a better way to join those columns into a DATE?
 
thanks
Marc
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: date creation

I have issues with Teams. It doesn't work well with me.

But send me your email and I'll send you a Zoom meeting invitation.

Ken Tyler





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


View solution in original post

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: date creation

Well, would need to better understand the data to say anything definitive. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

But I suppose you might need to check to make sure that you are not getting a text value or something odd for your Payment Day Related.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Super User II
Super User II

Re: date creation

VAR pay_year ='Payment Summary'[Payment Year]

VAR pay_month = 'Payment Summary'[Payment MONTH use]

VAR pay_day = 'Payment Summary'[Payment DAY related]

VAR pay_year_OK = pay_year >0

VAR pay_month_OK = pay_month > 0 && pay_month <=12

VAR pay_day_OK = pay_day > 0 && pay_day <=31

Result = IF(pay_year_OK && pay_month_OK && pay_day_OK,date(pay_year,pay_month,pay_day), "Error in year, month or day")

RETURN Result

 

You could put more complex logic on the limits for pay_day depending on the value of pay_month

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Highlighted
Helper V
Helper V

Re: date creation

Hi .. thank you so much for your quick response .. I am a fairly new user adn although I'm sure what you sent is fairly easy I am not entirely sure how to do replicate in my model. Would you be able to spend a minute via Teams so I can share my screen and you can talk me through it?

 

thanks

Marc

Highlighted
Super User II
Super User II

Re: date creation

I have issues with Teams. It doesn't work well with me.

But send me your email and I'll send you a Zoom meeting invitation.

Ken Tyler





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


View solution in original post

Highlighted
Helper V
Helper V

Re: date creation

Hi Ken

thanks so much... I can't use Zoom on my work laptop so I'm going to quickly transfer the file to my peronsal laptop.

 

My mail is: marcurdang@gmail.com

 

I should be ready in 15 minutes.

 

thanks so much

Marc

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors