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.
@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 –
The Simplest Way to Calculate Workdays between Two Dates
.
Let us know you’ve enjoyed the series by giving the post a and the video a on YouTube.
We will do our best to update this post as soon as video’s become available on the eDNA Channel.
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
I am totally new to this so apologies if I have missed the answer.
In the UK a company can start its financial year at any point.
@sam.mckay date table calculates the FY perfectly for this. Not sure about @Melissa, but I might be mis-using it.
If I run either query again with different dates and a different year start number, both queries create a new table.
Is it possible to simply update the current table once it is created?
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.
Ideally they would just use the same copy of the model and refresh the dates to reflect the different year.
If you are a literate in using Power BI, running the query again isn’t a big issue, but if you aren’t…
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.
In addition to @BrianJ response, here some additional remarks:
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
@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
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
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)
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.
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.
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
and the video a 
on YouTube.