Blank Transaction Date shown in Date Dim table

[THEG72]

Hi PBI Community,

I have a problem with my Date Dimension table showing transactions in my fact table.

When i run the transaction date from the fact table it shows the correct date as Sunday, 11th December 2016. However the Date Dimension Table is showing a blank row for this one transaction.

All other transactions are shown perfectly.

Does anyone have a similar experience? Could this be a bug in software? The data is definately in POWER BI but the date dimension table seems to be the source of the problem.

Can anyone suggest a fix ? Should i delete the DATE DIM and recreate it?

[Phil_Seamark]

What are the oldest and newest days in your DATE table?

[THEG72]

Hi Phill, thanks for taking time to reply to my question. Here is data from Datedim table.

First Date is 1 Jan 2013

DATE DIM TABLE RECORD 1

Last Date is 1 Jan 2026

DATE DIM TABLE RECORD 4746

[Phil_Seamark]

And you have a relationship between the two tables?  Are each of the columns in the relationship set to use the DATE datatype?

If one side is Date and the other DateTime you can encounter similar issues to this.  Just checking more than anything.

[Anonymous]

Hi @THEG72.

 >>Does anyone have a similar experience? Could this be a bug in software?

Yes, I faced this issue, but it is hard to reproduce the issue.

I'd like to suggest you to check the datasource mapping first, it may caused the issue.  If your data can be match well, the issue may related to power bi. Perhaps you can try to clean your cache file and recreate the report.

In addition, if above still not help, you can also submit a support ticket at below link.  (Right-Bottom -> "Create Support Ticket")

Support

Regards,
Xiaoxin Sheng

[THEG72]

Hi Xiaoxin

I sent you a link to the PBIX file if you want to review it.

[Anonymous]

Hi @THEG72,

You link has broke so I can't download it, can you upload to 1 drive and share the link?

Regards,

Xiaoxin Sheng

[THEG72]

Thanks for help but solved the issue, problem was my date table.

Cheers

[THEG72]

Hi Xiaoxin, thanks for your time.

I tried to clear the cache from the options / Global / Data Load and then Clear Cache.

I then deleted report and closed and restarted software.

I still end up with same issue. I can see the data in the journalrecord table from the accounting system

JOURNAL RECORD TABLE shows result

I can also list the transaction, using the journal record transction date from same table

When i use the DATEDIM table shown below, i get the blank for that 1 transaction.

What do i need to check for the data source?

I guess the last option is to do a test and delete the Datetable and start again?

[Phil_Seamark]

Any chance you can post your PBIX file (strip out all other tables) so we can have a play?

[THEG72]

Sent you an email with the link! Let me know if any issues accessing it

[Phil_Seamark · Accepted solution]

Hi there,

Your DATEDIM table is missing a record for the 11th of December 2016.  It looks to be an issue with the way you build it in M

You could try creating a DATE table in DAX using DATE = CALENDAREAUTO()

[THEG72]

Thanks for the help....! that solved it...