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

image
image.png807×199 10.3 KB

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

I created a simple example here

image
image.png795×252 11.4 KB

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.

#8

@mcnater - were you able to build this successfully?
@sam.mckay
I ran into similar problem where i attempting to calculate transactions per product by each week. When I use date table, it includes all the dates and thus weeks from entire date table range.

I would love to see if i can filter those dates and weeks that are not in the range of the fact table.

please let me know. thanks!

#9

This is a nice solution

#10

This isn’t ad but adds unnecessary code and logic in my opinion. I got the Start and End data stuff to work “most” of the time, but I still see random errors even though it seems to finish successfully. I haven’t been able to pin-point why it errors out sometimes and sometimes not with the same code. It’s very strange and buggy.