Calendar Table Issues

[cmcreynolds]

Hello and thanks for the help in advance-

I have data I'm pulling from Salesforce that has a created date; I have a calendar table that has the dates (no repeats) along with other fields I will need (week number, month names, etc.). I pull them both into the PowerPivot data model using PowerQuery.

I made the relationship but I'm not getting related data from the calendar table - everything shows up as (Blanks). So, I assume my issue is somewhere in the pipeline I don't have it formatted correctly (either date field - from SF or my calendar table). Which do I use, because I have made them the same format in the data model, PQ and in the original Excel worksheet (for the calendar table).

Insane Friday afternoon, please help.

 

[ImkeF]

Have you checked your model against the basic principles layed out here: Giving Back: “Steal” this Reference Card! - PowerPivotPro ?

Calendar table: page 6

Pivot table (report) layout: page 3 (row/column values from lookup tables and not from data table)

 

[cmcreynolds]

Yes, I've followed everything in that "reference card" (which is awesome, btw). I'm not even calculating with the dates, yet. I'm just trying to identify the week number (which is in the calendar table) - it just brings blanks when I use either "RELATED" or "LOOKUPVALUE". It's really blowing my mind with this one.

 

[ImkeF]

Problem is that intermediate steps often don't make sense. It's all about designing the final report. How should this look like (what's in rows, columns and value section)?

 

[cmcreynolds]

Yeah - and that is part of the issue - a CEO is not familiar with the data enough and keeps asking for metrics that don't make any sense - computationally or applicably

 

[scottsen]

Typically this is caused by having Date and Time in your data column (which, won't match the Date only... in your Calendar table).

 

[cmcreynolds]

BTW - I came across this issue again - it's solved by changing the format of the date in PQ instead of in the Data Model