cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmoizk Regular Visitor
Regular Visitor

convert date to integer

I have a dataset for a Date Dim (Integer date, full date datetype & all possible hierarcies ). I do a join to another datecolumn in a transtional dataset . Both are of datatype date same format (mm/dd/yyyyy). Join is not honoring its falling out. The other option i have is do a integer join, my datedim have datekey which is an integer and i need to convert my datecolumn in transtional data to a integer of format yyyymmdd. 

First why is my date join not honoring the join and if powerbi doesn't support it . How can i convert my date to integer to make use of integer join 

Thanks

 

I was able to get the date converted to integer using format and value function and then joined back to integer date in my date dim. But is date join not support in powerBI?

6 REPLIES 6
Super User
Super User

Re: convert date to integer

Date joins are supported - even preferred over SK joins. What do you meant "not honouring". Are you saying the join won't even complete, or it completes but then the behaviour is unpredictable?  Is there any time key information in one of the columns?  Is it possible you have rouge records in one of your date columns that are not dates?  Are both date columns from the same locale?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
JulianPayne_i4 Frequent Visitor
Frequent Visitor

Re: convert date to integer

i have the same problem - it is common - i can't believe there isn't a standard solution to this...

 

JulianPayne_i4 Frequent Visitor
Frequent Visitor

Re: convert date to integer

i have two datetime columns. i want to join on just Date. i covert each column from DatetTime do just date. i go to join on date, and the model does NOT parse - it cannot find a record match. i the background, SSAS is STILL storing the datetime info, even though i changed the datetime data type to date. so it does not drop the time info on the data point.

 

SO the model does not parse - so you have to make another tpye of key....

 

Highlighted
gselvag Regular Visitor
Regular Visitor

Re: convert date to integer

I have created a DateKey in my calendar datatable that work.

Try use following FORMAT ( [Date]; "YYYYMMDD" ) as DateKey column in your table. The value has to be converted to Whole Number by change column datatype.

graph_pbi3.JPG

JulianPayne_i4 Frequent Visitor
Frequent Visitor

Re: convert date to integer

this worked. i did the same thing, but did not include the " " around my format, so it did not takerecognize it. I tried to use 0000, but it gave the generic 4681 that it always gives

 

=FORMAT ([Date], "YYYYMMDD" ) does work to convert DateTime data format to text. 

 

Thanks for correcting my error!

gselvag Regular Visitor
Regular Visitor

Re: convert date to integer

The " " is used to name the column when create a calendar table.

As you create column it's name without " ". Am pleased to hear the you managed to resolve your issue with changetype. Please mark the issue as resolved. Thanks :-)

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 242 members 3,367 guests
Please welcome our newest community members: