Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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?
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.
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
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
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
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
in the visual settings (paint roller) set the Fiscal Year to Nov 1
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |