March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
1. First Step
Create 2 new column on your DATA report:
1. Month = MONTH(Table[Date])
2. Year = YEAR(Table[Date])
2. Fiscal Year
Creat a new column for Fiscal Year:
Fiscal Year = IF(AND([Year] =2012,[Month]<=12),"FY13",
IF(AND([Year] =2013,[Month]<=6),"FY13",
IF(AND([Year] =2013,[Month]<=12),"FY14",
IF(AND([Year] =2014,[Month]<=6),"FY14",
IF(AND([Year] =2014,[Month]<=12),"FY15",
IF(AND([Year] =2015,[Month]<=6),"FY15",
IF(AND([Year] =2015,[Month]<=12),"FY16",
IF(AND([Year] =2016,[Month]<=6),"FY16",
IF(AND([Year] =2016,[Month]<=12),"FY17",
IF(AND([Year] =2017,[Month]<=6),"FY17",
IF(AND([Year] =2017,[Month]<=12),"FY18",
" ")))))))))))
3. Fiscal Quarter
FiscalQrt=IF(([MonthOfYear]>=1 && [MonthOfYear]<4),"FQ3",
IF(([MonthOfYear]>=4 && [MonthOfYear]<7),"FQ4",
IF(([MonthOfYear]>=7 && [MonthOfYear]<10),"FQ1","FQ2")))
4. Month Short Name
MonthShortName=IF([Month]=1,"1-Jan",
IF([Month]=2,"2-Feb",
IF([Month]=3,"3-Mar",
IF([Month]=4,"4-Apr",
IF([Month]=5,"5-May",
IF([Month]=6,"6-Jun",
IF([Month]=7,"7-Jul",
IF([Month]=8,"8-Aug",
IF([Month]=9,"9-Sep",
IF([Month]=10,"10-Oct",
IF([Month]=11,"11-Nov","12-Dec")))))))))))
The simpliest way to do that (ex: create a column with "2021-2022", etc.) is to substracting 3 months to the main Date (which means starting the year in April). For exemple, for any date:
FiscalYear=year(DATEADD('Table'[Date], -3, MONTH))&"-"&year(DATEADD('Table'[Date], -3, MONTH))+1
Hello,
I've tried to use the following section to create a Fiscal Year column:
Fiscal Year =
IF(AND([Year] =2017,[Month]>3),"FY17/18",
IF(AND([Year] =2018,[Month]<4),"FY17/18",
IF(AND([Year] =2018,[Month]>3),"FY18/19",
IF(AND([Year] =2019,[Month]<4),"FY18/19",
IF(AND([Year] =2019,[Month]>3),"FY19/20",
IF(AND([Year] =2020,[Month]<4),"FY19/20"," ")))))))
I've tried to do this via creating a custom column and as a measure. Neither is working.
The error when creating a custom column is "Token RightParen Expected". However I have all the right parenthesies I need'; adding or subtracting them makes no difference to the error.
The error in a measure relates to Month and Year not being recognised.
Is anyone able to help me?
My aim is to end up with a silcer that has three check boxes in it. The first filters to FY17/18, the next to FY18/19, the third to FY19/20.
If anyone can help me I'd really appreciate it. Thanks
My Fiscal year runs from April 1 to March 31. I found this message very useful in solving my needs. Here is what I did
Step 1.
Create 2 columns, Month and Year (for my purposes I used a column called Received Date so my columns are titled the same
1. Received MONTH = tblAssessments[ReceivedDate].[MonthNo]
2. Received YEAR = tblAssessments[ReceivedDate].[Year]
Step 2.
Create a Fiscal Year Column without having to have YEAR be a calculation of the IF statement
- (Note: Concatenate is limited 2 items in Power BI, I would have preferred to add a "/" between the years
This thread was immenselty helpful. Thank you!
1. First Step
Create 2 new column on your DATA report:
1. Month = MONTH(Table[Date])
2. Year = YEAR(Table[Date])
2. Fiscal Year
CreatE a new column for Fiscal Year:
FY = IF(AND('Table'[Month Sort]>=4,'Table'[Month]<=12),'Table'[Year]+1,'Table'[Year])
Hope this helps. It works for any year...
1. First Step
Create 2 new column on your DATA report:
1. Month = MONTH(Table[Date])
2. Year = YEAR(Table[Date])
2. Fiscal Year
Create a new column for Fiscal Year:
FY = IF(AND('Calendar'[Month Sort]>=4,'Calendar'[Month Sort]<=12),'Calendar'[Year]+1,'Calendar'[Year])
Hope this helps...
This was really helpful and effective, without having a large Dax
* Great Solution!
* You can do similar leveraging the Conditional Column . Not as Dynamic as DAX, but perhaps a little easier for those not ready to enter into the world of DAX.
* Excited for MSFT to make this a control within the Application ~~ Anticipating by the end of FY Q2 of '18 🙂
.
Cleaned up a little and added finincial year and financial yr/qtr, adjust as required:
DateTable = ADDCOLUMNS (
CALENDAR (DATE(2011,1,1), DATE(2018,01,01)),
"Day",FORMAT([Date],"DDDD"),
"Year", FORMAT([Date],"YYYY"),
"Month", FORMAT([Date],"MMMM"),
"Quarter", SWITCH( MONTH([Date]),
1, "Qtr4",
2, "Qtr4",
3, "Qtr4",
4, "Qtr1",
5, "Qtr1",
6, "Qtr1",
7, "Qtr2",
8, "Qtr2",
9, "Qtr2",
10, "Qtr3",
11, "Qtr3",
"Qtr3" ),
"MthYr",CONCATENATE(FORMAT([Date],"MMM"),FORMAT([Date]," YYYY")),
"QtrYr",CONCATENATE(SWITCH( MONTH([Date]),
1, "Qtr4",
2, "Qtr4",
3, "Qtr4",
4, "Qtr1",
5, "Qtr1",
6, "Qtr1",
7, "Qtr2",
8, "Qtr2",
9, "Qtr2",
10, "Qtr3",
11, "Qtr3",
"Qtr3" ),FORMAT([Date]," YYYY")),
"FinancialYr", SWITCH(TRUE(),Month([Date])<4,YEAR([Date])-1 &"-"&YEAR([Date]),YEAR([Date])&"-"&YEAR([Date])+1),
"QtrFinYr", CONCATENATE( SWITCH( MONTH([Date]),
1, "Qtr4",
2, "Qtr4",
3, "Qtr4",
4, "Qtr1",
5, "Qtr1",
6, "Qtr1",
7, "Qtr2",
8, "Qtr2",
9, "Qtr2",
10, "Qtr3",
11, "Qtr3",
"Qtr3" )," "&SWITCH(TRUE(),Month([Date])<4,YEAR([Date])-1 &"-"&YEAR([Date]),YEAR([Date])&"-"&YEAR([Date])+1))
)
I added the Month & Year columns to my data.
Then I copied the Fiscal Year Query in my custom column creator.
I got the "no syntax errors" confirmation and clicked to proceed.
Upon trying to complete this column addition I'm getting:
Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly.
= Table.AddColumn("Fiscal Year", each IF(AND([Year] =2012,[Month]<=12),"FY13",
IF(AND([Year] =2013,[Month]<=6),"FY13",
IF(AND([Year] =2013,[Month]<=12),"FY14",
IF(AND([Year] =2014,[Month]<=6),"FY14",
IF(AND([Year] =2014,[Month]<=12),"FY15",
IF(AND([Year] =2015,[Month]<=6),"FY15",
IF(AND([Year] =2015,[Month]<=12),"FY16",
IF(AND([Year] =2016,[Month]<=6),"FY16",
IF(AND([Year] =2016,[Month]<=12),"FY17",
IF(AND([Year] =2017,[Month]<=6),"FY17",
IF(AND([Year] =2017,[Month]<=12),"FY18",
" "))))))))))))
Please advise. What am I missing?
Hi there,
Thank you for posting this. It did give me the idea on how to work this out. However, I think I have made it even more dynamic. My FY is April - March. Here is the formula that I used which works as well but it is dynamic:
FY = IF(FY_Calendar[MonthNo]<=3,"FY"&RIGHT(FY_Calendar[Year]-1,2),"FY"&RIGHT(FY_Calendar[Year],2))
Just what I needed, thanx!
I've put some of this together to make a generic DateTable creator like so, just need to work out how to make 3 month rolling indicators etc:
Simply update the date range required in the dates marked bold below (format is Year, Month, Day) then paste in a new table formula bar:
DateTable = ADDCOLUMNS (
CALENDAR (DATE(2011,1,1), DATE(2018,01,01)),
"Day",FORMAT([Date],"DDDD"),
"Year", FORMAT([Date],"YYYY"),
"Month", FORMAT([Date],"MMMM"),
"Quarter", SWITCH( TRUE(),
MONTH([Date]) = 1, "Qtr4",
MONTH([Date]) = 2, "Qtr4",
MONTH([Date]) = 3, "Qtr4",
MONTH([Date]) = 4, "Qtr1",
MONTH([Date]) = 5, "Qtr1",
MONTH([Date]) = 6, "Qtr1",
MONTH([Date]) = 7, "Qtr2",
MONTH([Date]) = 8, "Qtr2",
MONTH([Date]) = 9, "Qtr2",
MONTH([Date]) = 10, "Qtr3",
MONTH([Date]) = 11, "Qtr3",
"Qtr3" ),
"MthYr",CONCATENATE(FORMAT([Date],"MMM"),FORMAT([Date]," YYYY")),
"QtrYr",CONCATENATE(SWITCH( TRUE(),
MONTH([Date]) = 1, "Qtr4",
MONTH([Date]) = 2, "Qtr4",
MONTH([Date]) = 3, "Qtr4",
MONTH([Date]) = 4, "Qtr1",
MONTH([Date]) = 5, "Qtr1",
MONTH([Date]) = 6, "Qtr1",
MONTH([Date]) = 7, "Qtr2",
MONTH([Date]) = 8, "Qtr2",
MONTH([Date]) = 9, "Qtr2",
MONTH([Date]) = 10, "Qtr3",
MONTH([Date]) = 11, "Qtr3",
"Qtr3" ),FORMAT([Date]," YYYY")
))
Thanks for the post, I have adjusted for use in the UK - please like if useful:
1. First Step
Create 2 new columns on your DATA report:
1. Month = MONTH(Table[Date])
2. Year = YEAR(Table[Date])
2. Fiscal Year
Create a new column for Fiscal Year:
Fiscal Year = IF(AND([Year] =2013,[Month]<=3),"FY12",
IF(AND([Year] =2013,[Month]<=12),"FY13",
IF(AND([Year] =2014,[Month]<=3),"FY13",
IF(AND([Year] =2014,[Month]<=12),"FY14",
IF(AND([Year] =2015,[Month]<=3),"FY14",
IF(AND([Year] =2015,[Month]<=12),"FY15",
IF(AND([Year] =2016,[Month]<=3),"FY15",
IF(AND([Year] =2016,[Month]<=12),"FY16",
IF(AND([Year] =2017,[Month]<=3),"FY16",
IF(AND([Year] =2017,[Month]<=12),"FY17",
" "))))))))))
3. Fiscal Quarter
Create a new column for Fiscal Quarter:
Fiscal QTR = IF(([Month]>=1 && [Month]<4),"FQ4",
IF(([Month]>=4 && [Month]<7),"FQ1",
IF(([Month]>=7 && [Month]<10),"FQ2","FQ3")))
This provides:
2013 | Qtr 2 | June | FQ1 | FY13 |
2013 | Qtr 3 | July | FQ2 | FY13 |
2013 | Qtr 3 | August | FQ2 | FY13 |
2013 | Qtr 3 | September | FQ2 | FY13 |
2013 | Qtr 4 | October | FQ3 | FY13 |
2013 | Qtr 4 | November | FQ3 | FY13 |
2013 | Qtr 4 | December | FQ3 | FY13 |
2014 | Qtr 1 | January | FQ4 | FY13 |
2014 | Qtr 1 | February | FQ4 | FY13 |
2014 | Qtr 1 | March | FQ4 | FY13 |
2014 | Qtr 2 | April | FQ1 | FY14 |
2014 | Qtr 2 | May | FQ1 | FY14 |
2014 | Qtr 2 | June | FQ1 | FY14 |
All the best, Oli
Thanks Oli. That was helpful.
For those of you looking to create a relative Quarter Offset - I used this:
Relative Quarter Offset = (4*YEAR([Date]) + ROUNDDOWN(MONTH([Date])/3,1) - (4*YEAR(TODAY()) + ROUNDDOWN(MONTH(TODAY())/3,1)))
Where 'Date' came from my Dim Date table...
This means the current quarter returns a zero - the last quarter returns - 1 etc etc... works a treat!
(Quarters based on Jan > Mar, Apr > June, Jul > Sept & Oct > Dec)
Fiscal Year Quarter Cut Off works well and it applies to all no matter when the Fiscal Year start, April or October.
If you are looking for Fiscal Year CutOff to dynamically display measures of "Current Fiscal Year", "Previous Fiscal Year" and etc., Here are the steps and DAX will work. Please comment if this works, Thank you.
Note: Fiscal Year Start from April 1st in this example.
Fiscal Year Number= If(
Month(Date[date])<= 3,
Year(Date[date])-1,
Year(Date[date])
)
Current Fiscal Year Number = If( Month(today())<= 3,
Year(today())-1,
Year(today())
)
Fiscal Year CutOff = Date[Fiscal Year Number]-Date[Current Fiscal Year Number]
"Fiscal Year CutOff" = 0, Current Fiscal Year
"Fiscal Year CutOff" = -1, Previous Fiscal Year
...
I needed to bring back transactions that are in the current fiscal year. Here is what I did:
1. Created a column called Fiscal Year:
Fiscal Year =
IF(Month(PROJEMPLTRANS[TRANSDATE]) < 11, Value(FORMAT(PROJEMPLTRANS[TRANSDATE], "YYYY")), VALUE(FORMAT(PROJEMPLTRANS[TRANSDATE], "YYYY")) +1)
2. Created a column called Year:
Year = Year(PROJEMPLTRANS[TRANSDATE])
3. Created a column called "IsCurrentFiscalYear"
IsCurrentFiscalYear = If((Year(NOW()) = PROJEMPLTRANS[Fiscal Year]) || (Year(NOW()) = PROJEMPLTRANS[Year] - 1),1,0)
Now in my report I simply filtered by the value 1 on the IsCurrentFiscalYear column.
Thanks,
Tom - Stoneridge Software
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
93 | |
72 | |
58 |