Forum Discussion
Why does two calendars not work in power pivot when related to different date field in one table ?
Hello,
I have one calendar related to the [inception date] in the source table and another calendar related to the [settlement date] in the same table, in order to generate two pivot tables with different timings of the same amount.
Creating a results pivot table of records and their amounts according to the table [inception date] works perfectly, i.e. using the dates hierarchy of calendar 1.
Problem, arises when I attempt to create a results table of the records and their amounts according to the [settlement date] in the table. The pivot table created does not recognize the second calendar and returns the records with amounts and 'blank' as the calendar header.
Why does this happen ? How is one able to get two different timings of an amount from one source table ?
Thank you for your help.
Perhaps I misunderstand something, let play with the sample. We have source data and two linked calendars
and built on them Pivot Tables as in attached file
is that like what you do?
6 Replies
Multiple date tables is the standard approach in data modeling and everything shall work if the model is built correctly. First in mind is incorrect relationships, however perhaps something else.
Thank you for your prompt answer.
I double checked the relationship between the second Calendar (created from within the data model) and the Input Table added to the data model and confirmed the relationship was correctly established between the dates.
I found that the [second date] in the source table was a formula that calculated the value of the date based, and that even-though the [second date] field was formatted as a date in the data model, when creating the pivot table with the second Calendar as the Date Hierarchy , it returns a column header for the values of 'Blank' instead bucketing the value into monthly buckets. Does this mean that the input at source has to be a hardcoded date or is there a way to recognize the [second date] as a valid piece of data representing a date ?
Is there is Microsoft material on learning how to efficiently built data models ?
Many thanks.
As for materials. Not sure about ones directly from Microsoft, but I'd recommend all what Italians, Marco Russo and Alberto Ferrari, are publishing. Check their site https://www.sqlbi.com/ and it's worth to read their Definitive Guide to DAX and other books.
