Forum Discussion

Wilhelm7870's avatar
Wilhelm7870
Frequent Visitor
11 years ago
Solved

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

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...
  • Anonymous's avatar
    Anonymous
    10 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
11 years ago

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's avatar
    erop
    Regular Visitor
    11 years ago

    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's avatar
      Anonymous
      Not applicable
      11 years ago

      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's avatar
        amien
        Helper V
        10 years ago

        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