Reusable calendar - with additional data loaded from local data source.

[Anonymous]

Hi all ,  thanks for any help.

Previously I have had either a calendar built with power query as part of the model, but when I tried to use that in the service it didnt allow an scheduled data load of the other data from local sql servers? Are the two not allowed? So I left that solution.

Then I tried a locally held sql calendar to overcome the not liking the power query in the scheduled upload - this would work - but it means I upload a lot of data.

Would the best solution be a really long calendar locally on sql containing all possible dates and any additional columns that must be gotten from local data. This gets uploaded and then dax or power query widens this into other columns like month name , year and month etc. Dax at least would not interfer with the scheduled refresh?

So the upload from sql is still long (and cut to start and end dates in the model as part of the load) , but at least is as narrow as possible and is widened again as part of the load.

Just trying to come up with the most efficient and easily resuable way of getting a calendar into multiple power bi models? Am i right in the data refresh restriction?

Any pointers greatfully received? Thanks

[v-alq-msft]

Hi, @Anonymous 

Based on your description, If you do not have a Date table in your data source, you can create such a table directly in the data model by adding calculated columns to a table built by using either CALENDAR or CALENDARAUTO. These functions return a table of one column of Date data type. For example, CALENDARAUTO automatically finds the minimum and maximum year of all the date columns included across the whole data model, and generates all the dates included between these years.

https://docs.microsoft.com/en-us/dax/calendar-function-dax

https://docs.microsoft.com/en-us/dax/calendarauto-function-dax

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks - I could yes use calandarauto - and then join in my local date data to this table. But would there be a way to share this- rather than having to do it over in ever differant model?

Thankyou

[amitchandak]

Just my thoughts

If your SQL calendar is all sorted with the calculation you need, you can always use that. Another way is to get a pbix created with only a calendar table with all needed calculations and then use a copy of that for any new model. For already in use. You may have to move the calculations.

[Anonymous]

Thanks - how would I use a copy of a calendar pbix - I cant connect to two power bi datasets can I? Thanks