You just need to make sure the date used above matches the first date in your list.

Simply replace the 100 in the formula with this Power Query code substituteĭuration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2015,1,1)) We need to change this new line of code to create the dynamic end date. For now we will just select 100 days.īut we don’t actually want 100 days, we want a “dynamic end date”. Note how Power Query gives you some help about the function you just typed in.Įnter a start date, the number (count) of days you want and the granularity of the calendar (step 1 means daily), and press OK. Note that the Power Query Language is case sensitive so you must capitalise correctly.Īnd then press the “Invoke” Button. The first step is to use the Power Query Language in the formula bar to create a list of dates. You will need this so you can easily see the Power Query Code that is generated by the UI tools, and also to allow you to edit the code by hand when needed. If you haven’t done so already, turn on your Formula bar from the view menu. The Blank Query option is right at the bottom of the “From Other Sources” menu. First Create a New Blank Power Query Workbook. You can write some snappy Excel functions in your source calendar table to code around this, or you could make your DAX more complex, but better still – I think this is a great opportunity to learn some new skills – Power Query.īuilding a custom calendar from scratch in Power Query is actually quite straight forward, and I am going to take you through it step by step below. It is a pain to change the import filter on your Excel calendar each time you refresh (not practicable actually). There is one problem with this approach however – the date over runs when you are looking at last years’ data in a pivot.Īs you can see above, we have dates into the future that are showing sales for last year with no sales this year – because we haven’t had “this year” yet. Historically I have just used an Excel workbook that I created with all the relevant columns, and enough rows of dates to cover for the next year. There are lots of different ways of creating a custom calendar for use in your Power Pivot workbooks, and plenty of discussion about them on (25 prior to this one).

If you follow the steps below, you will get a good sense of how capable Power Query is, and may spark your interest to learn more about it. In this post, I am going to show you how simple it is to create a custom calendar using Power Query.

Excel 2016 query editor blank fields