Forum Discussion

admin11's avatar
admin11
Memorable Member
4 years ago
Solved

Year field return null value ?

Hi All

I have a DATE field , it return correct date. 

I have create a master calendar table .

I have link sales table with master calendar.

But when i create a table , year field return null value.

Hope some can share with me where go wrong ?

 

My PBI sample file :-

https://www.dropbox.com/s/6cq0a9lfhyx8711/PBTI_V008.pbix?dl=0

 

Paul

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    This is because the date column you have here is text, which means the relationship does not work as expected.

     

    Try redefining the column as VALUE ( SALES_I[AR Invoice Date] ) or

    DATE (
	RIGHT ( SALES_I[AR Invoice Date], 4 ),
	LEFT ( SALES_I[AR Invoice Date], 2 ),
	MID ( SALES_I[AR Invoice Date], 4, 2 )
)

    and make sure the column type is date or date/time.

     

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    This is because the date column you have here is text, which means the relationship does not work as expected.

     

    Try redefining the column as VALUE ( SALES_I[AR Invoice Date] ) or

    DATE (
	RIGHT ( SALES_I[AR Invoice Date], 4 ),
	LEFT ( SALES_I[AR Invoice Date], 2 ),
	MID ( SALES_I[AR Invoice Date], 4, 2 )
)

    and make sure the column type is date or date/time.

     

    • admin11's avatar
      admin11
      Memorable Member
      4 years ago

      AlexisOlson 

      appreciated very much , it work fine. 

      may i know it is possible to to remove the time stamp ? only display DD/MM/YYYY ? Now it display DD/MM/YYYY 00:00:00

       

      Paul