- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Fiscal Year - April to March
Hi,
I've been stuck on a project for a while and I've had a problem which I thought was simple but I'm having some problems with. I'm working with data from 2015 and I want to show the fiscal year from April to March for each of the years. I'm able to get April to December but the main problem that I'm facing is the cross over in years for the months January, Febrary and March.
Any help will be appriciated
Thanks,
Mike
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A column with this cal should allow you to sort perfectly across year and month ranges. The only problem is that the date column of this table will not sort on this. You might have to create a month-Year Col for display
SortCol = CONCATENATE(if([Date].[MonthNo]> 3,DateTable[Date].[Year],DateTable[Date].[Year]-1) ,format(if(DateTable[Date].[MonthNo]> 3,DateTable[Date].[MonthNo]-3,DateTable[Date].[MonthNo]+9),"00"))
Please download the file again from dropbox, which I uploaded. I have corrected fiscal Year cal, created month-year and sort col. Changed the graph and the year filter.
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The way how I approach FY is to create table for 'central' date dimension (Modeling, New Table).
Once created, I add relations to all other date columns of other tables.
Usually I have more columns (for variety of use cases), but I have stripped them out for clarity:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Check -
https://community.powerbi.com/t5/Desktop/Previous-Fiscal-Year-TOTALYTD/m-p/775039#M373381
https://docs.microsoft.com/en-us/dax/totalytd-function-dax
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
From what I can see, that's using it as a measure. I want to use it as a Date/Time data type so I can use it like so...
So for the year 2019, it will show April to March instead of January to December
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think that better to create a calendar table like that. You can generate on in the data model view.
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Just tried to do that, I think I'm doing something wrong. I created a new data table, created a relationship to my main dataset and it's not having it.
This is what it looks like before I create a relationship between the two tables...
And this is what it looks like after
Not sure what I'm doing wrong
I'll add the PBIX file
https://www.dropbox.com/s/y2uggwbh58rvqg2/Disqualified%20Leads%20-%20Test.pbix?dl=0
Cheers
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I checked the file and there was no relationship created.
I created it between DateTable.Date and Lead.Month.
Once you do this, you should use date dimensions from DateTable only.
Yo have to do it like this:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I checked the file. The original one. First I created a created_date field by removing the time part and joined it with time. Then I rebuild the chart
Rebuild second chart
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, @amitchandak . Not sure what you mean by this part though
" First I created a created_date field by removing the time part and joined it with time"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
PFA
https://www.dropbox.com/s/zy78idsv21n2v2z/Disqualified%20Leads%20-%20Test.pbix?dl=0
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ahhh, I see what you've done. Good stuff. Getting closer
Do you know how I would go about sorting the months so it's April to March instead of January to December?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
As suggested above - use column "Yr-Mo" which is there on purpose (because it allows sorting). If you created new column with "mmm" notation, make this column sorteable by column "Yr-Mo".
Select this new column: Modelling, Sort by Column, select Yr-Mo. Voila.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry fella's, struggling to follow. Are you saying I need to create a new column in the Date Field with the Year and Fiscal month merged in it?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A column with this cal should allow you to sort perfectly across year and month ranges. The only problem is that the date column of this table will not sort on this. You might have to create a month-Year Col for display
SortCol = CONCATENATE(if([Date].[MonthNo]> 3,DateTable[Date].[Year],DateTable[Date].[Year]-1) ,format(if(DateTable[Date].[MonthNo]> 3,DateTable[Date].[MonthNo]-3,DateTable[Date].[MonthNo]+9),"00"))
Please download the file again from dropbox, which I uploaded. I have corrected fiscal Year cal, created month-year and sort col. Changed the graph and the year filter.
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Generate a number like YYYYMM and in time table or the required tables. and put the sorting of the column on that column in the data model view.
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
MM should be based on the fiscal year. Means April is 01 and March is 12. Also, the year remains same in that fiscal year
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
07-18-2024 08:12 AM | |||
11-16-2024 03:56 AM | |||
07-29-2024 02:57 PM | |||
05-24-2023 08:27 AM | |||
06-10-2024 11:18 AM |
User | Count |
---|---|
104 | |
75 | |
46 | |
39 | |
33 |
User | Count |
---|---|
165 | |
90 | |
66 | |
46 | |
43 |