date creation

[MarcUrdang]

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

[kentyler]

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

[MarcUrdang]

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

[kentyler · Accepted solution]

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

[MarcUrdang]

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

[Greg_Deckler]

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.