Additional Functionality

[Guavaq]

Hi there firstly this connector is really great and I think that it can only get better with time.

What I have found is that when I have been creating a date table there are two additional things that helps define the date table.

1. Is the ability for the end user to decide when the Date table will end. It could be the end of the current year, or 4 years in advance. With the refreshing of the Date table via the Query Editor, this can change on each and every refresh.

2. Also the capability to be able to define when the Fiscal Month starts. As here in Australia it starts from the 7th month, whilst when I was living in South Africa it was the 3rd month.

Fortunately I have had a blog post in which I have got the M-Code in which I have the parameter for the Fiscal Start Month, which it then uses to calculate the Fiscal Month numbers, Fiscal Years, etc.

As well as the code for when I want my Date table to end.

You can find it here: https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fiscal-attributes-using-power-query/

If you have any questions please let me know, happy to assist.

Regards Gilbert

[migueesc123]

Hey Gilbert!

Thanks for the kind words and for taking the time to contribute!

I'm working on the end_date parameter for the Calendars and hopefully I'll have that by the end of this week. In regards to the capability to define the Fiscal Month start, you can actually do that right now, but instead of defining when your Calendar starts, you define when it ends. The 2nd parameter of the Fiscal Calendar requires a YearEnd example, so you need to select from the date picker a date as an example. I believe I used 30-Jun of a random year on the video from my blog post (it can be any year. What matters is the actual day and month). So, for example, you can input the parameters like this: And it'll automatically calculate a fiscal calendar where your year ends on March 31st. Changing the 2nd parameter defines in what day/month your fiscal year ends.

A future release will have another option, imagine a new button in the 'Get Data' dialog, to handle Custom Fiscal Calendar that have scenarios like:

• End the fiscal year on the last [weekday] (like saturday) of the month
• End the fiscal year in the [weekday] (like saturday) nearest to the end of the month
• End the fiscal year on the Last Date of the Month (specific for the scenario in SA)

Let me know what you think!

[Guavaq]

Thanks Miguel,

My apologies for not understanding it all 100%, you have done some fantastic work.

Another thing that might be of interest is where I have created additional calculated columns for when it is the Current Day, Current Week, Rolling Week, Current Month etc. I have found this helpful for when the users want to be able to filter their data so that it will dynamically update based on the columns above. Whist the Relative Date Slicer is good, it can sometimes not give the custom requirements that the users want. And by having these columns it makes it easy for the user to put this as a filter.

I am happy to Share that code with you? And I would think at the end of the day if the users do not want the columns they can simply remove them. But better to have the additional functionality.

Regards Gilbert

On Wed, Aug 30, 2017 at 9:48 AM, Miguel Escobar notifications@github.com wrote:

Hey Gilbert!

Thanks for the kind words and for taking the time to contribute!

I'm working on the end_date parameter for the Calendars and hopefully I'll have that by the end of this week. In regards to the capability to define the Fiscal Month start, you can actually do that right now, but instead of defining when your Calendar starts, you define when it ends. The 2nd parameter of the Fiscal Calendar requires a YearEnd example, so you need to select from the date picker a date as an example. I believe I used 30-Jun of a random year on the video from my blog post (it can be any year. What matters is the actual day and month). So, for example, you can input the parameters like this: [image: image] https://user-images.githubusercontent.com/9544580/29848789-7dca23a4-8ce8-11e7-9b00-372aae07ba2c.png And it'll automatically calculate a fiscal calendar where your year ends on March 31st. Changing the 2nd parameter defines in what day/month your fiscal year ends.

A future release will have another option, imagine a new button in the 'Get Data' dialog, to handle Custom Fiscal Calendar that have scenarios like:

• End the fiscal year on the last [weekday] (like saturday) of the month https://en.wikipedia.org/wiki/Accounting_period#Last_Saturday_of_the_month_at_fiscal_year_end
• End the fiscal year in the [weekday] (like saturday) nearest to the end of the month https://en.wikipedia.org/wiki/Accounting_period#Saturday_nearest_the_end_of_month
• End the fiscal year on the Last Date of the Month (specific for the scenario in SA) https://en.wikipedia.org/wiki/Fiscal_year#South_Africa

Let me know what you think!

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/migueesc123/CalendarCreator4PowerBI/issues/2#issuecomment-325836547, or mute the thread https://github.com/notifications/unsubscribe-auth/AeACvdTOhEmz7AUOHdTkJgl7gyXswCwQks5sdKNmgaJpZM4PGrRP .

[migueesc123]

if anything, I should be the one apologizing. I haven't posted a single line of documentation to this repository :(

I've just updated the connector and it has the custom EndDate parameter now. Here's how the windows look like right now:

For now, my main objectives are:

• [ ] Find out if the current release of this connector works correctly for everybody
• [ ] Create a new option to output a Custom Calendar using the ISO 8601 rules
• [ ] Create a new option to output a Custom Calendar using the logic of: End the fiscal year on the last [weekday] (like saturday) of the month
• [ ] Create a new option to output a Custom Calendar using the logic of: End the fiscal year in the [weekday] (like saturday) nearest to the end of the month
• [ ] Create a new option to output a Custom Calendar using the logic of: End the fiscal year on the Last Date of the Month (specific for the scenario in SA)

I actually do add those columns of current day, week, month and such to my queries, but I do see this as something REALLY REALLY custom that should be added by the end-user in the event that they want to manage the UTC offset and things like that.

[Guavaq]

No worries I guess you were excited to get it out there and get people testing.

I can confirm that it does work perfectly for me.

I like the changes that you have made those are good, as well as the new functionality.

For sure it does get tricky when there is the UTC offset as it all depends where the people are coming from, so as you suggested best to leave that as a custom column that they could add. I in the past have changed the UTC offset in the data rather, which then means that the date table can function as expected.

On Wed, Aug 30, 2017 at 11:15 AM, Miguel Escobar notifications@github.com wrote:

if anything, I should be the one apologizing. I haven't posted a single line of documentation to this repository :(

I've just updated the connector and it has the custom EndDate parameter now. Here's how the windows look like right now: [image: image] https://user-images.githubusercontent.com/9544580/29850499-dbb9fe48-8cf3-11e7-96b2-c51ee93de108.png [image: image] https://user-images.githubusercontent.com/9544580/29850514-efecb5ea-8cf3-11e7-8bc7-ef8820bddb4c.png

For now, my main objectives are:

• Find out if the current release of this connector works correctly for everybody
• Create a new option to output a Custom Calendar using the ISO 8601 rules
• Create a new option to output a Custom Calendar using the logic of: End the fiscal year on the last [weekday] (like saturday) of the month https://en.wikipedia.org/wiki/Accounting_period#Last_Saturday_of_the_month_at_fiscal_year_end
• Create a new option to output a Custom Calendar using the logic of: End the fiscal year in the [weekday] (like saturday) nearest to the end of the month https://en.wikipedia.org/wiki/Accounting_period#Saturday_nearest_the_end_of_month
• Create a new option to output a Custom Calendar using the logic of: End the fiscal year on the Last Date of the Month (specific for the scenario in SA) https://en.wikipedia.org/wiki/Fiscal_year#South_Africa

I actually do add those columns of current day, week, month and such to my queries, but I do see this as something REALLY REALLY custom that should be added by the end-user in the event that they want to manage the UTC offset and things like that.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/migueesc123/CalendarCreator4PowerBI/issues/2#issuecomment-325849177, or mute the thread https://github.com/notifications/unsubscribe-auth/AeACveOTC4TRB8sx8Z92Sbz4IJXSYzc6ks5sdLeXgaJpZM4PGrRP .