convert date to integer

[mmoizk]

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?

[gselvag]

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.

[JulianPayne_i4]

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!

[ArneHoefkens]

You could wrap a VALUE function around it
= VALUE(FORMAT ([Date], "YYYYMMDD" ) )

[gselvag]

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 🙂

[JulianPayne_i4]

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

[MattAllington]

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?

[JulianPayne_i4]

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