Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
I have a question around connecting to systems that hold multiple calendars.
My issue is that I want a report that presents sales totals by month for a fiscal year.
However my Dynamics database contains 2 companies, 1 US and 1 UK, the UK one runs off of a Jan - Dec calendar where as the US one runs April to March.
Is it possible to have a single report that can present different year axis when switching between company ID's?
Or is it best practice to develop seperate report for each fiscal calendar in use?
Thanks
Solved! Go to Solution.
Hi @wickenss,
For your requirement, you can add a calendar table, then add calculate column to calculate the financial year of specify company.
Sample:
DateTable= CALENDAR(DATE(2015,1,1),TODAY())
Add calculate columns:
US Financial Year = [Date].[Year]&" FY" UK Financial Year = YEAR(DATE([Date].[Year],[Date].[MonthNo]-3,[Date].[Day]))&" FY"
Create a hierarchy column with above financial years and drag it to the axis field.
After above steps, you can switch the display financial years by drill down button.
Regards,
Xiaoxin Sheng
Hi @wickenss,
For your requirement, you can add a calendar table, then add calculate column to calculate the financial year of specify company.
Sample:
DateTable= CALENDAR(DATE(2015,1,1),TODAY())
Add calculate columns:
US Financial Year = [Date].[Year]&" FY" UK Financial Year = YEAR(DATE([Date].[Year],[Date].[MonthNo]-3,[Date].[Day]))&" FY"
Create a hierarchy column with above financial years and drag it to the axis field.
After above steps, you can switch the display financial years by drill down button.
Regards,
Xiaoxin Sheng
Thanks Xiaoxin Sheng,
This makes sense, I'll give it a go!
Steve
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |