Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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")))))))))))
This is really helpful, thanks. We have a July-June fiscal year, so I had to adjust the "IsCurrentFiscalYear" column to:
IsCurrentFiscalYear = if((year(Now())='Date'[FiscalYear]-1 || year(now())+1='Date'[FiscalYear]),1,0)
@sbstern wrote:This is really helpful, thanks. We have a July-June fiscal year, so I had to adjust the "IsCurrentFiscalYear" column to:
IsCurrentFiscalYear = if((year(Now())='Date'[FiscalYear]-1 || year(now())+1='Date'[FiscalYear]),1,0)
hello,
I am trying to use your formula, but i am stuck and not able to understand.
My Fiscal (Financial) year is from April to March.
We call FYear as FY1314 ( 1stApril 2013 to 31st march 2014)
I need to Come up with IsCurrentFYear, IsCurrentMonth.
Please help me. Thanks
Current Fin Year = if((year(Now())=dCalender[Financial Year]-1 || year(now())+1=dCalender[Financial Year]),1,0)
DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.
Hi @rkalantri
You just need to change the first line from
the first number value in that formula needs to change.
<7 is for a financial year that goes from 1st July to 30th June.
Fiscal Year = IF(Month(DetailedDates[Date]) < 7, Value(FORMAT(DetailedDates[Date], "YYYY")), VALUE(FORMAT(DetailedDates[Date], "YYYY")) +1)
so to get it from April to March you just need to change the "< 7" in the above to "< 4"
So the financial year starts in the 4th month.
Rgds
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
Hi,
This formula isn't working for me. When I try creating the column it asks for me to add a third requirement for the IF function. If I try adding one it then returns the error of too many requirements. Can you help me at all?
Error: "Argument '3' in IF function is required."
It appears that you are, in fact, missing an argument. It should be If then, and else. You have all of the ifs and thens but you are missing an else. Put a ,0 before you begin the parenthesis at the end and see if that works for you.
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", 0 )))))))))))
Proud to be a Super User!
For that many conditions SWITCH statements are so much easier to read and write
Internally they are transformed into nested IFs
EDIT: No need for all the opening "(" and closing ")" that comes along with the nested IFs
MonthShortName = SWITCH ( TRUE (), [Month] = 1, "1-Jan", [Month] = 2, "2-Feb", [Month] = 3, "3-Mar", [Month] = 4, "4-Apr", [Month] = 5, "5-May", [Month] = 6, "6-Jun", [Month] = 7, "7-Jul", [Month] = 8, "8-Aug", [Month] = 9, "9-Sep", [Month] = 10, "10-Oct", [Month] = 11, "11-Nov", "12-Dec" )
This will be better, maybe?
MonthShortName=Format([Date], "mm-mmm")
Solution for calculate the “Fiscal Year”, without date ranges condition (dynamic solution).
Scenario:
The formula for calculate the Fiscal Year for the “X” company is from July 1st to June 30th of the next year.
Example:
Expression
Fiscal Year =
IF ‘Date’ Month <= 6,
THEN ‘Year’
ELSE ‘Year’ + 1
DAX Expression
Note: The expected Output is “The phrase FY plus the last 2 digits of the Fiscal Year”
DAX Formula:
Fiscal Year =
CONCATENATE("FY",
IF(MONTH('Table'[Date]) <=6,
VALUE(FORMAT('Table'[Date],"YY")),
VALUE(FORMAT('Table'[Date],"YY")) +1
)
)
Table = Table Name
Date = Date Column Name
In case anyone else needs the PowerQuery formula for this:
Text.Combine({"FY",
if Date.Month([Month]) <=6
then Text.From(Date.Year([Month]))
else Text.From(Date.Year([Month]) +1 )
})
This works but the display isn't showing the "0" for years '00 to '09. Can you help me out? Thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |