Forum Discussion
Why does two calendars not work in power pivot when related to different date field in one table ?
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?
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.
SergeiBaklanThank you.
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?
Thank you Sergei for breaking it down into smaller pieces - I see that the lead time field which I included as the variable between the start date and the end date was based on an average in the historic data, and hence not a whole number (as you have used in the example file), consequently the pivot table returned the header as 'blank' instead of the month and year. I will adjust the model accordingly.
Many thanks.