The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I'm open to any suggestions regarding the subject but ultimately we do our normal reporting based on our financial year - ranging from April to March 1 - 12. I've already got my financial year & normal calendar year etc set up along with merging them together in the reports to create a financial year hierarchy and calendar year hierarchy.
My initial thought was to try and create a slicer with the options "Financial Year" & "Calendar Year" and selecting one of those will alternate the charts & graphs accordingly (similar to how i've set up previous custom slicers to alternate what measures are being reported) with no luck unfortunately. This funcionality will save a lot of time and rework when we start to forecast different scenarios each month (e.g. financial plan = financial year basis, CST/Base and other stress scenarios = calendar basis, Q2/3/4 etc will also be financial basis).
Unfortunately I can't post the data for various reasons but ultimately it's showing forecast positions for different products over the next 5 years. In Excel we would simly build out the IF statement to say if X = financial year then sum/average between these dates and if X = calendar year then between a different set of dates. I'm hoping there is something relatively simple in BI that I'm just completely missing!!
Thanks
Hey @Anonymous
So this is possible if your visuals are all based on measures that are relative to the date. Then you can create a measure for FY vs Calender Y. Then you just do exactly as you stated and create the slicers.
My thought is though, why not just duplicate the page and in the duplicate change your calendar to the alternative Year.
Or, duplicate the visuals in the report and change the calendar to FY.
Really though I think what you are looking for is grouping: https://community.powerbi.com/t5/Service/Grouping-reports-dashboards-groups-subgroups/td-p/275693
Let me know if that helps.
Unfortunately they aren't relative to date...
So I have a number of columns in my data set that effectively reference:
FY
FY Period
Cal Year
Cal Period
etc.
In BI I'll drag and drop the FY Period under FY to give me my FY hierachy, again for Cal Year & Period. It's not that they are being used as a Fin vs Cal. One scenario will call for all the charts and graps on all the tabs to be shown on a financial year basis (in which case I would select the X axis to be the FY hierarchy) and again the same steps if the scenario required Cal year.
Ideally I would have some way of adjusting the X axis or tables without having to go into the report and change all the pages, so that if FY and the month is Dec it will show as FY XXXX & period 9, whereas if the selection for Cal year was made it woulld show FY XXXX & Period 12.
I can't seem to reference the columns holding the FY Period, FY Year or Cal Period, Cal Year when trying to create my measure (I'm assuming that's because they aren't measures based on dates but actually stored as values in a column instead?)
Many thanks