How to create customized calander

Question

Hi all,&nbsp;I need to show the monthly and Quarterly trends for some of the KPIs. Now the issue I am facing is my organisation follows customised calander. So for Example Dec'16,Jan'17 and Feb'17 will be Q1'17. Dec'16 will be from 3-12-2016 to 30-12-2016, Jan'17 will be from 31-12-2016 to 27-01-2017, Feb'17 will be from 28-01-2017 to 03-03-2017.&nbsp;I have the date fields in my raw data. As of now I am mapping the dates in the excel file using a vlookup to get the corresposnding month and Quarter.&nbsp;Is there any way in which I can create a calander in my data itself in Power BI as per my requirements?Is there any what to convert the text formatted month and quater that I pull up in excel into any format which can give me a cronological trend in the charts that I am showing&nbsp;attaching a view of the chart that I have built and that is not in chronological order&nbsp;Snapshot of the raw data - "surveyDate" is the field i get from raw data. FM and FQ are the fields that I have looked up into excel and have imported in PowerBI

kenpuls · Answer

If there is no pattern to the dates, then it's going to be pretty difficult to built an M script to lay out the calendar. &nbsp;5-4-5 we can work with (although that is one I've never heard of... seems a week too long...), but if it's not that this year, then what are the rules? &nbsp;Someone must have decided on what periods go where, so that must have some kind of logic. &nbsp;As long as you can draw out the logical rules, then I'm sure we can build something to handle it.&nbsp;Failing that, the only method I can see to get around it is to either:-Build it manually in Excel and call it as a data source, or-Build it manually in Power BI Desktop using the Home --&gt; Enter Data feature to build a manual table.&nbsp;Must admit, the latter does not excite me, as it will be tedious to create and even worse to maintain.

anonymous · Answer

Your intuitions here are correct. &nbsp; You need to&nbsp;somehow get a thorough calendar table built and imported. &nbsp;Could be that calendar comes from a csv file, a database table, or writing Power Query / M to generate it... a bunch of options, but as it's custom there is no "out of box" solution for you.&nbsp;KenPuls&nbsp;probably has a post on generate custom calendar in M if I had to guess. &nbsp;ImkeF&nbsp;you know of one?

kenpuls · Answer

I've probably got an example somewhere, yes. &nbsp;Is this just a 4-4-5 calendar, or is there some other pattern to the dates?

anonymous · Answer

KenPuls- &nbsp;There is no fixed pattern of 4-4-5 to the dates. in the prvious financial year it was 5-4-5.&nbsp;so need some other way around this!!&nbsp;Anonymous&nbsp;- can you please guide me to any of the steps that you have suggested to get a way around?

Forum Discussion

How to create customized calander

4 Replies

Related content

How creating a Python custom library

Creating a custom column

create custom column concatenated

Create a Calander

Creating Custom columns