Create Calendar only from Dates contained in Data


#1

I’ve got a nice calendar that I use all the time that gets created in the Query Editor. It does all the things you’d expect and I’ve added columns like WEEK_START and etc. The calendar take to inputs in the beginning, a START_DATE and an END_DATE so that it knows how to make the calendar. While this is nice, what I’d REALLY like is for the calendar creation to be dynamic. That is, is there a way that my Calendar creation code can somehow reference the MAX and MIN Dates in the main data of my app, and then only create a calendar within those parameters?

Meaning, if my Sales Data has Dates from 3/1/2018 through 9/30/2018, can I pass those dates somehow into a step so the calendar will only be created from 3/1 - 9/30, and not whatever my previous START and END Dates would have had to be setup as.

I use to do this in Qlikview with a sub routine, and I’d love to be able to do it here. I think you might be able to something in DAX similar, but I like having the calendar in the Query Editor instead.

Thoughts?

Thanks in advance!


#2

Yes this can be done with query parameters.

I haven’t actually got an example of this year myself, but it certainly is possible.

You see here is this code

Basically you need to find a way to capture the startdate and enddate and feed them into here.

I created a simple example here

But you need this to be dynamic.

Check out here on how to complete it. This is the best example I’ve found.


#3

Thanks. That seemed to work. Question on it. Does it matter if the Calendar query comes after the other query with the main data? How does PBI know what order to do it in? I’ve never been clear on that.


#4

That’s a really good question actually.

I’m not 100% sure on the exact order.

All I know is that it seems to optimize for any scenario thrown at it. So it likely recognizes that it requires an input and then calculates that during the proceed.

Quite amazing how it all comes together in complex models.


#5

Ya agreed. I see now if you go to “View–> Query Dependencies” that it does put it in the proper order. I forgot about that screen and can verify there that it works correctly. Thanks!


#6

Yes that’s a good screen to view BUT that doesn’t actually tell the order in which everything happens internally within the power query engine, it just shows you want dependencies there are between queries.

It’s very interesting nonetheless and a bit hidden in my view.


#7

BOh I misunderstood that screen then. Thanks for clarifying.