Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?)
Solved! Go to 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
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
@Seth_C_Bauer, 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?
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.
@Seth_C_Bauer 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
@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
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.
Here's a solution using a calculted table in just 2 steps
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.