How to create source data, e.g. a 'master calendar'?

[Wilhelm7870]

Is it possible to 'create' a data source using 'query'? I'm trying to create a 'master calendar' that simply needs to contain one column with dates going back, say 3 months, and forward, say 6 months.  The idea is to use this to make consistent charts against complete timelines and not only for the dates that appear in my data source.  (Or alternatively, is there a way to 'fill' any gaps in data from source?)

[Anonymous]

Wilhelm,

Sounds like you want a date dimension table...

You can create one in multiple ways, this link contains a solution to create a date dimension table in Power Query and references other solutions by other authors as well.

I'm assuming "query" means power query, but if you want a solution in SQL, this builds the table with input parameters. I've used this one alot. SQL Date Dimension

[erop]

@Anonymous, this works well in my Excel files. But what about implementing the same technique in Power BI Desktop? Since upgraded DAX specification has CALENDAR function I think it should be easy. But have no idea how to use it in PBID files neither with "old" Power Query nor newly CALENDAR function. Could someone drop a line about?

[Anonymous]

@erop

You could create a calender table by itself in Excel and just import that as a seperate table into your PBID model. Then create the relationships to your fact tables in PBID.
As for dynamically building it, I don't have a pre-built solution that I've tested out in PBI.

[amien]

@Anonymous Thanks for the link for sqlservercentral. I have one additional question. It will create a function and when you hit invoke, you can enter two dates (start and end). 

Question : how would it be possible to make the start and end date dynamic based on the loaded fact table? So if i could put the min date and max date in some kind of variable and use it in the just created calender function

[Anonymous · Accepted solution]

@amien is your fact table in SQL as well? Or does it come from an independent source?

Just taking a quick look at this again, if everything is in SQL, you can make the code that populates the date dimension tables a procedure and pass in the min/max from the fact table +/- additional months (easy enough to add to the dates you return from the fact table.

Would look something like

Sproc1 - SSCentral script changed to procedure with input parameters for the dates.

Sproc2 -

1) Select start and end date from the fact table 

2) Execute the date dimension procedure with the values from the fact table

[Greenrjc]

Hi 

I have a related query (I think)

Each time I create a new report I have to re-processes the Query for my Calendar Date Table.

Is there anyway to automate this process or to ensure each New report loads the edited Calendar Date Table automatically?

Currently I have a notepad pinned to desktop with the code to copy paste every time.

[frederik]

Here's a solution using a calculted table in just 2 steps

 http://kohera.be/nl/blog/business-intelligence-nl-2/how-to-create-a-date-table-in-power-bi-in-2-simp...