Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
michael_knight
Post Prodigy
Post Prodigy

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

1 ACCEPTED SOLUTION

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.

View solution in original post

16 REPLIES 16
Joc008
Frequent Visitor

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:

 

dim_Date = ADDCOLUMNS (
CALENDAR (DATE(2000;4;1); DATE(2030;3;31));
"Year"; YEAR ( [Date] );
"Yr-Q"; FORMAT ( [Date]; "YY-\QQ" );
"Yr-Mo"; FORMAT ( [Date]; "YY-MM" );
"FY"; "FY" & FORMAT ( EDATE([Date];-3); "YY" );
"FY_rel"; YEAR(EDATE([Date];-3)) - YEAR(EDATE(TODAY();-3));
"mnth_rel"; YEAR([Date])*12+MONTH([Date])-YEAR(TODAY())*12-MONTH(TODAY());
"Months ago"; var m=[Date]-TODAY() return if(m<-90;"3 mnths or older";if(m<-60;"2-3 mnths";if(m<-30;"1-2 mnths";"...-1 mnth")))
)

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...

help 5.PNG

 

help 6.PNG

 

So for the year 2019, it will show April to March instead of January to December

I think that better to create a calendar table like that. You can generate on in the data model view.

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...

help 11.PNG

And this is what it looks like after

help10.PNG

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

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:

http://prntscr.com/ozhxyp

Tried that and it doesn't work with the visuals

Help 2.PNG

Does it work for you? @Joc008 

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

Screenshot 2019-08-30 14.40.35.pngScreenshot 2019-08-30 14.40.55.png

 

Rebuild second chart

Screenshot 2019-08-30 14.43.03.png

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"

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? 

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.

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? 

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.

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.

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.