cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
klehar
Helper IV
Helper IV

Changing date quarters to match company's quarters

Hi,

 

I have a date column that I can perfectly use for all types of breakdowns by quarter/month etc

I have also been using it with timeline cusotm visual from marketplace. It looks like this:

klehar_0-1631808519174.png

 

Now the tricky client requirements comes in where he wants to analyze the data not be calendar year and quarter but by fiscal year and quarter

The fiscal year starts each november. So if it is Nov 2021, the Fiscal Year become 2022 and quarter becomes Q1

If I were to use the above timeline chart or any date slicer for that matter, I would get Year as 2021 and quarter as Q4.

Its easy to make fiscal quarters and months using M code but how do I adjust the same in my slicers and visuals?

 

As an example for better understanding, If I click on 2021 Q1 in the above timelien slicer, I should get my sales data for Nov 2020, Dec 2020 and Jan 2021 and not for Jan 2021, Feb 2021 and Mar 2021.

Is it possible with less data gymnastics or possible at all in the first place?

 

8 REPLIES 8
lbendlin
Super User
Super User

You can create your own column hierarchies if you so desire. Personally I have never seen the need for these.  Even in a slicer visual you can now add multiple columns for an ad-hoc hierarchy.

lbendlin_0-1631972040069.png

 

 

I would recommend you look at your Power BI dataset in DAX Studio.  What you will find is that the standard "Auto Date/Time"  function creates a lot ( A LOT ) of utterly useless and space eating ... stuff  that you may not actually want to have in your dataset.

@lbendlin I see one disadvantage here

 

klehar_0-1631974765259.png

 

 

Look below

 

I have 2 slicers

 

1st slicer i have constructed using Fiscal Year stacked on top of fiscal quarter stacked on top of fiscal month and i do not get a range filter for that

 

Now case 2, look at the date datatype filter which is slicer on the right

It get a range on its own

 

Not only this if i put slicer 2 on the filtet shelf I can also do advanced filtering like relative dates and last n period dates

 

klehar_1-1631974857533.png

 

I want to make sure that my fiscal year, quarter and date also gets a range on the slicer and it also gets relative time slicing capabilities. Is that possible?

 

I see what you mean

lbendlin_0-1631975331107.png

But that's an entirely different ask.  You may want to raise an idea to enable inclusion of fiscal periods in the relative date period selection.

Sure i will do that but thanks for your insights

lbendlin
Super User
Super User

in the visual settings (paint roller) set the Fiscal Year to Nov 1

lbendlin_0-1631928142185.pnglbendlin_1-1631928197820.png

 

Thanks for the reply.

I figured that out but what if I dont want to use this visual. Is there a generic solution to this problem?

The generic solution is to have your own Calendar table (preferably external) with your fiscal year, quarters, month and weeks identifiers for each date.

@ibendlin So i have created a custom table as well that has calendar date.

Along with that I have fiscal month, fiscal quarter and fiscal year.

 

But will that mean I have to use 3 different columns everywhere I want to analyse my data using dates?

In case of calendar date, I just had to drag the date column on the axis against any measure and i was done

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors