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.
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.
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
attaching a view of the chart that I have built and that is not in chronological order
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
Your intuitions here are correct. You need to somehow get a thorough calendar table built and imported. 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.
@KenPuls probably has a post on generate custom calendar in M if I had to guess. @ImkeF you know of one?
I've probably got an example somewhere, yes. Is this just a 4-4-5 calendar, or is there some other pattern to the dates?
@KenPuls- There is no fixed pattern of 4-4-5 to the dates. in the prvious financial year it was 5-4-5.
so need some other way around this!!
@Anonymous - can you please guide me to any of the steps that you have suggested to get a way around?
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. 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? Someone must have decided on what periods go where, so that must have some kind of logic. As long as you can draw out the logical rules, then I'm sure we can build something to handle it.
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 --> Enter Data feature to build a manual table.
Must admit, the latter does not excite me, as it will be tedious to create and even worse to maintain.
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.