Loose my column dates when adding values or rows in matrix

[ctedesco3307]

Hello.  Can anyone tell me why when I add in my rows field I loose most of my weeks in my columns?  

I’m trying to show a matrix of sum oof billable hours (values) across weeks(columns) by project (rows)

I have my week nums in the column

But when I add in my values

Or worklog ID

I only get the last 2 weeks.  I guarantee there I data for these worklogs for the whole year – I can’t figure out why all the weeks aren’t showing up – there are no filters applied anywhere

I have my WORKLOG_DATE mapped to the Date in my Calendar date table as a many to One 

I'm sure it;s something stupid I'm just not seeing. Thanks in advance 

[ctedesco3307 · Accepted solution]

This is the solution to this topic posted by @TheoC 

Okay, so I figured out what the cause is.  The Date field in your Calendar table is fine. But the Date field in your Fact table is actually a DateTime field.  You need to create a Date field on its own and then create a relationship between the two tables with the Date fields only  

[ctedesco3307 · Accepted solution]

This is the solution to this topic posted by @TheoC 

Okay, so I figured out what the cause is.  The Date field in your Calendar table is fine. But the Date field in your Fact table is actually a DateTime field.  You need to create a Date field on its own and then create a relationship between the two tables with the Date fields only  

[TheoC · Accepted solution]

Hi @ctedesco3307 

Glad I could help with the "Okay, so I figured out what the cause is.  The Date field in your Calendar table is fine. But the Date field in your Fact table is actually a DateTime field.  You need to create a Date field on its own and then create a relationship between the two tables with the Date fields only "

In all honesty, I don't know the exact reason that the Date and Date/Time didn't work initially.  It could have been for a variety of reasons but within this example, I am unsure.

All the best with your Power BI journey my friend.

Theo 🙂

[ctedesco3307]

Hi @TheoC - Ok - I took your suggestion one step further and created a date columns using Datevalue(worklog_worklog_date) and that worked! So I ammend my above post to confirm you provided the correct solution, although i don't understand why both TDate and Worklog_Worklog_Date being date/time or both being Date don't work. That doesn;t make sense to me. If you can explain that - would be super helpful - if not - ok.  THANK YOU for the solution. 

[TheoC]

Hi @ctedesco3307 

Okay, so I figured out what the cause is.  The Date field in your Calendar table is fine. But the Date field in your Fact table is actually a DateTime field.  You need to create a Date field on its own and then create a relationship between the two tables with the Date fields only (see below screenshot).

I am sending you a link to PBIX via private message 🙂

Hope this helps 🙂

Theo

[TheoC]

@ctedesco3307 are you able to mark the above post as the solution?

[ctedesco3307]

@TheoC - Sure - you need alot of rows though to se all the worklogs against all the calendar dates - and I can't attach files to these posts. is there a better way to get you what you need? 

Let me know if this works - you have to request a share when you click on it 

@TheoC 

https://docs.google.com/spreadsheets/d/1eVgDR3sAf2UjzkhCQgcz5XqXnjTxjwjAWiQfrgYi5P8/edit#gid=6767988... 

[TheoC]

@ctedesco3307 just gained access. Will touch base shortly.

[TheoC]

Hi @ctedesco3307 

A couple of things just to double check:

1. The relationship from the Date to Fact table is one to many (one from Date, many to Fact table).
2. Ensure that your Date table has all of the dates required in it.
3. On your visual, make sure the Week Numbers is from your Date table.

Hope this helps 🙂

[ctedesco3307]

Hi @TheoC - Thank you for the quick response, but all the things you mentioned are in tact. 

1. The relationship from the Date to Fact table is one to many (one from Date, many to Fact table). - it is -- please see image in post 
2. Ensure that your Date table has all of the dates required in it. - it does. It ha every day in the calendar year 
3. On your visual, make sure the Week Numbers is from your Date table. - It is - 
4. 

Thank you for trying to help! 

[TheoC]

Hi @ctedesco3307 

Can you send some sample data?

Thanks heaps,

Theo

[ctedesco3307]

Hi @TheoC  Thank  you for trying once again, but unfortunaley that is not it. The  join between the two tables is between WOrKLOG_WORKLOG_Date and Date and they are both set as Date/Time. If one of those is showing as date for the column type it must have happened in the sample file creation.  I did try changing them both to date to see if it made a difference and it did not.  Thanks anyway