Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
topdar
Helper I
Helper I

How to create customized calander

Hi all,

 

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

Capture.JPG

 

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

Capture.JPG

4 REPLIES 4
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.