Time Intelligence series by BrianJ and Melissa

Hi everyone,

We’ve brought the Time Intelligence series we did for Enterprise DNA TV together in this post so it’ll be easier to find and navigate through completely. :wink:

@BrianJ and I had great fun doing these. If you have any questions or concerns about the content covered in these video’s please create a new topic, refer to the video and @mention BrianJ or Melissa. But we’d also welcome your feedback within this thread of course. – THANK YOU –

  1. How to create a Holiday table
  1. M function for the Extended Date table
  1. Date Harvest Deep Dive
  1. Setting up a Dynamic Start- and Enddate for Power Query Date tables
  1. Using the Extended Date table: ISAFTERTODAY
  1. Using the Extended Date table: OFFSETS
  1. The Simplest Way to Calculate Workdays between Two Dates

.

Let us know you’ve enjoyed the series by giving the post a :heart: and the video a :+1: on YouTube.
We will do our best to update this post as soon as video’s become available on the eDNA Channel.

.
Want to request a video?
You can leave your suggestions in the YouTube/Blogs/Other category.

10 Likes

Are these videos going to be added to the Time Intelligence section of the EnterpriseDNA Site??

You Tube videos can’t be downloaded anymore - at least I can’t download them.

Guy

@Guy,

I believe so. I see that some already have been added to the portal site:

  • Brian

I don’t see them yet - maybe later this week

Amazing work on this

Please see the above videos to learn more. To ask any questions on the content covered in this tutorial please start a new topic within the forum. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum

Hi,

I am totally new to this so apologies if I have missed the answer.

  1. In the UK a company can start its financial year at any point.

  2. @sam.mckay date table calculates the FY perfectly for this. Not sure about @Melissa, but I might be mis-using it.

  3. If I run either query again with different dates and a different year start number, both queries create a new table.

  4. Is it possible to simply update the current table once it is created?

  5. An accountant might be producing accounts for more than one client. All of the records will be in a common database, but each clients FY will start on a different date.

  6. Ideally they would just use the same copy of the model and refresh the dates to reflect the different year.

  7. If you are a literate in using Power BI, running the query again isn’t a big issue, but if you aren’t…

Thank you for your help,

Paul

@kellysolutions,

Yes, @Melissa’s Extended Date Table handles fiscal year every bit as well as the previous date table code, and adds a lot of additional functionality. @sam.mckay now fully recommends using the Extended Date Table instead of his previous code, and does so in all of his reports as well.

You can easily change any of the initial parameters on the date table without creating a new table. Just go into Power Query, select your Dates table, and look to the first line of M code per below:

You can change any of these parameters at any time just by editing directly in that code line. For example, I currently have my fiscal year for this report starting in January, but if I wanted to change that to July I would just change the third parameter (see red arrow above) from a 1 to a 7. Similarly, the start date, end date and holiday table reference can be changed in the same ways. In addition, Melissa has another video in the series about changing the start and end dates to be dynamic.

I hope this is helpful. Please give a shout if you have any other questions.

Thanks.

  • Brian
1 Like

Hi @kellysolutions,

In addition to @BrianJ response, here some additional remarks:

  1. In the UK a company can start its financial year at any point.
    RE: That’s no issue as long as that’s the first day of the FYStartMonth

  2. @sam.mckay date table calculates the FY perfectly for this. Not sure about @Melissa, but I might be mis-using it.
    RE: both date table functions share the same basic FY logic

  3. If I run either query again with different dates and a different year start number, both queries create a new table.
    RE: Correct, these are function queries returning a table

  4. Is it possible to simply update the current table once it is created?
    RE: Yes, see video #2 and #4 and the video linked in the Extended date table topic (links in the initial post #1 above)

  5. An accountant might be producing accounts for more than one client. All of the records will be in a common database, but each clients FY will start on a different date.

  6. Ideally they would just use the same copy of the model and refresh the dates to reflect the different year.
    RE: Suggestion. Add a Parameter to your model for the FYStartMonth, so you can set that for each separate model from within Power BI Desktop without having to change that value in PQ.

  7. If you are a literate in using Power BI, running the query again isn’t a big issue, but if you aren’t…
    RE: Will not be required if you have that parameter in place

I hope this is helpful.

2 Likes

Thank you both (@BrianJ) for replying so quickly. I will take another look at all of this and what you have suggested

Paul

1 Like

Thanks everyone for your replies here. Really helpful. Yep I fully agree and use the updated date table from Melissa now in all my development.

Over coming months we’ll be making updates to portal content regarding this as well.

Sam