Power Pivot Calander Table returning blank

[ViaanParticipant Started: 1Replies: 2Total: 3]

Hi there,

I have joined the Power Pivot party quite late and very excited about the possibilities. I have been working through Power Pivot and Power BI 2nd edition and am trying to create some measures that work out DatesMTD calculations.

I am running Excel Pro Plus 2016.

I have my data set which has a single date column amongst others – confirmed format of the column is date
I have created a Power Query (or more like copied from this forum) to create a Date Table – I have confirmed the DateKey is Date format and Dates are a contigious set (pretty much taken care of by the query).
Both Tables have been added to the Data Model.

I have made sure that the Date Table is set up as a Date Table in the Power Pivot Design with the DateKey as the lookup field.

I have created a relationship between the two fields and checked that the direction is as expected (not that I had much option to set it up any other way).

Simple measure – countrows(Data) created on Data Table

Create Pivot with Rows = DateKey from Date table and Values = Simple Measure

I get a return of Blank – 2222 (total row count)

My pivot does not seem to be recognising the date field from the Date Table as a date.

Can anyone help me make head way on this… been at this for 3 days now.

Thanks heaps in advance.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hi Viann,

What makes you think that the pivot is not recognizing the date field from the date table as a date?

Some other things to consider:

One important requirement is that there is no time component in either of your date fields that participate in the relationship.

Also important is that your calendar table cover the same period as your data table.

If your date table is named any thing other than Calendar, please ignore the following: For 2016, if your calendar table is named Calendar that you should always reference it in formulas as ‘Calendar’ and always reference fields as ‘Calendar'[MyFieldName] (instead of Calendar[MyFieldName]). In 2016, Calendar has become a key word in the DAX language and early on I experienced some grief with models imported from Excel 2013 with date tables named Calendar.

Although probably not the issue here, you should always mark your date table as a date table (see Design tab on ribbon and Mark as Date Table). This will also give you the opportunity to confirm that the DateKey column is of type date.

If none of the above works, can you attach a sample workbook?

[ViaanParticipant Started: 1Replies: 2Total: 3]

Hi there  Tom,

I have converted the rawdata date field to dd/mm/yyyy.

My Date Table is call Dates.

When I try and add the DateKey to the rows in my pivot table, I dont get date related options. If I perform date calculations on the columns I get an error that I cannot convert text to date even though the column is formated as Date.

Sample attached.

 

Kindest regards,

 

Viaan

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hi Viann,

Restore Date column has non-zero time components in addition to year, month and day.

Neither the date column in the dates table nor the date column in data column can have time components other than 0.

Please see attached workbook. I used TRUNC( [Restore Date] ) to create a calculated column in your data table with dates whose time components are zero. Since TRUNC() returns a number, I set the column format to Date to display as a Date. The new column is named [Restore Date (No Time Comoponent)].

I removed the existing relationship between Dates and Data and created a new relationship between the calculated column described in the previous paragraph and the Date column in the Dates table.

Afterwards, the pivot works as I expected it to.

Tom

Attachments:

You must be logged in to view attached files.

[ViaanParticipant Started: 1Replies: 2Total: 3]

Now that is a neat trick!!!
Thanks heaps… and i have learned a new formula!

[Author]

Posts