Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I have the following date table that I use as a 'StartDate' & 'EndDate'. Here is the formula that creates the date table currently:
StartDate = ADDCOLUMNS(CALENDAR("1-1-2013","31-12-2017"),"DateAsInteger",FORMAT([date],"YYYYMMDD"),"Year",YEAR([Date]),"Monthnumber",FORMAT([Date],"MM"),"YearMonthNumber",FORMAT([Date],"YYYY/MM"),"YearMonthShort",FORMAT([Date],"YYYY/mmm"),"MonthNameShort",FORMAT([Date],"mmm"),"MonthNameLong",FORMAT([Date],"mmmm"),"DayOfWeekNumber",WEEKDAY([Date]),"DayOfWeek",FORMAT([Date],"dddd"),"DayOfWeekShort",FORMAT([Date],"ddd"),"Quarter","Q" & FORMAT([Date],"Q"),"YearQuarter",FORMAT([Date],"YYYY" & "/" & FORMAT ([Date],"Q")) )
I would like to add in Fiscal Year and Fiscal Quarter.
The Fiscal Year begins July 1 and ends June 30.
I would like to display the Fiscal Year as "FY13-14" for (July 1, 2013 thru June 30, 2014) and etc. and the Fiscal Quarter I would like to display as "FQ1" "FQ2" etc.
Can anyone help me provide the lines I would add to the above formula to achieve this?
Thank you in advance!
Chris
Solved! Go to Solution.
For the fiscal year
1. Create a FiscalYearNumber column as
FiscalYearNumber=If( Month([Date]) >= 7 , Year([Date]),Year([Date]) -1 )
FiscalYearDisplay = ="FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2)
Create a column called FiscalMonth
FiscalMonth=(If( Month([Date]) >= 7 , Month([Date]) - 6,Month([Date]) + 6 )
Now FiscalQuarterNumber = ROUNDUP ([FiscalMonth]/3,0)
FiscalQuarterDisplay= "FQ" & format([FiscalQuarterNumber],"0")
Try it out.
If it works please accept this as a solution and also give Kudos.
Hi all! I am new to PowerBI, where do you put this code in to get it to work? I'm trying to do a fiscal year that starts July 1st. I only have one table in my dashboard, can someone tell me how to append the one table rather than having to do a second table?
I have the exact same problem. Would love know this.
Hi @Anonymous ,
Try this
Worked like a charm. I will mark your post as a solution, once I figure out how. Thanks again!!!
Hi
I have followed the instructions by @cjc322 which worked a charm, thank you so much. I would like to take this to the next stage and would like to add a calcualted colum, which flags if today date in within the present Fiscal quarter. i have used to show current month :
IsCurrentMonth =
IF (
YEAR ( DateCalendar[Date] ) = YEAR ( TODAY () )
&& MONTH ( DateCalendar[Date] ) = MONTH ( TODAY () ),
"Yes",
"No"
)
But struggling with current FIscal Month, and therefore fiscal year, mu fiscal year starts april
any help would be much appriciated
TIm
Hi @tmears
Please try the following
1. Create a column in your DateTable
FiscalYearMonthNumber = DateTable[FiscalYearNumber]*100+DateTable[FiscalMonth]
2. Create a measure called CurFiscalYear
CurFiscalYear = If( Month(Today()) >=4 , Year(Today()),Year(Today() -1 )
3. Create a measure called CurFiscalMonth
CurFiscalMonth = If( Month(Today()) >= 4 , Month(Today()) -4,Month(Today()) + 4 )
4. Create a measure called
CurFiscalQr =ROUNDUP ([CurFiscalMonth]/3,0)
5. Create a measure called
CurFiscalYearMonth = [CurFiscalYear]*100 + [CurFiscalMonth]
6. Create a Column in DateTable
IsCurMonth = if(DateTable[FiscalYearMonthNumber] = [CurFiscalMonth],"Yes","No")
7. Create a column in DateTable
FiscalYearQuarterNumber = DateTable[FiscalYear] *100 + DateTable[FiscalQuarterNumber]
8. Create a measure called
CurFiscalYearQuarterNumber = [CurFiscalYear]*100 + [CurFiscalQr]
9. Create a Column in DateTable
IsCurQr = if(DateTable[FiscalYearQuarterNumber ] = [CurFiscalYearQuarterNumber],"Yes","No")
10. Create a column in DateTable
IsCurYear = if(DateTable[FiscalYear] = [CurFiscalYear],"Yes","No")
You can use these columns IsCurQr, IsCurMonth,IsCurYear in your filter expressions.
If this works for you please accept it as a solution and also gice KUDOS.
Cheers
CheenuSing
ChennuSing
THANK YOU!!!!!
well we are nearly there... however the iscurmonth, iscurqr and is curyear is showing No for all records
Hi @tmears
Can you share your date table in dropbox or onedrive and provide the link.
How is your datetable created, the script for that.
Cheers
CheenuSing
Dear Sir,
i Created This But I want to This Calendar Table Join to Transaction Table.How is it Possible.
Because this table is not Seeing in Query Editor.Please Help me
For the fiscal year
1. Create a FiscalYearNumber column as
FiscalYearNumber=If( Month([Date]) >= 7 , Year([Date]),Year([Date]) -1 )
FiscalYearDisplay = ="FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2)
Create a column called FiscalMonth
FiscalMonth=(If( Month([Date]) >= 7 , Month([Date]) - 6,Month([Date]) + 6 )
Now FiscalQuarterNumber = ROUNDUP ([FiscalMonth]/3,0)
FiscalQuarterDisplay= "FQ" & format([FiscalQuarterNumber],"0")
Try it out.
If it works please accept this as a solution and also give Kudos.
Hi @CheenuSing
How if i want to add 1 column to show and categories what fiscal year by month
like
April 1, 2023 - March 31, 2024 = FY24
April 1, 2024 - March 31, 2025 = FY25
April 1, 2025 - March 31, 2026 = FY26
.......
.......
thank's and appreciate
I am having problems with the line below. Do both lines get entered when creating the column? When I try to do this I get the syntax error which is alos below. If Have the just the first line it works fine but the second line is giving the syntax error.
FiscalYearNumber=If( Month([Date]) >= 3 , Year([Date]),Year([Date]) -1 )
FiscalYearDisplay = ="FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2)
The syntax for '=' is incorrect. (DAX(="FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2))).
Hi @Allan77R2V1,
In the sceond formula, the two "=" sign should be replaced with one "=" sign. Typo error.
Thanks for pointing out. Appreciate that.
Cheers
CheenuSing
Feeling a little late to the party here but wondering if anyone can help:
I am trying to apply this code:
Superb!! Thanx...:)..
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |