Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
In the power query there is already a column with date column.
I wanted a help to create Custom calendar table with custom start date ,end date of year ,Quarter, Periods
Eg: For fiscal year start date :July 6th 2021 ,
fiscal year end date:July 2nd 2022 ,so this is fy2022
For Quarter 1 start date :6th July 2021 ,Q4 end date is July 2nd 2022,this will be like Q1 ,Q2 ,Q3 ,Q4
For Period P1 start date:6th July 2021 ,P12 end date is July 2nd 2022, Periods are like months P1 to P12
By Knowing current year start date and end date all FIscal Year ,Quarter ,Period needs to aligned
In addition previous year data like fy 2021 ,fy 2020 ,fy 2017 whatever present should also be dynamically created.
Period | Quarter | Fiscal Year | Date |
p1 | q1 | fy2022 | 06-jul-21 |
p1 | q1 | fy2022 | 07-jul-21 |
p12 | q4 | fy2022 | 02-jul-22 |
like this for all periods ,quarters fiscal years months(all years)
Hi @SrikanthD013 ,
You can create a calculated table as below:
Calendar =
VAR FiscalStart = "07"
VAR STARTYEAR = "2021"
VAR ENDYEAR = "2022"
RETURN
ADDCOLUMNS (
CALENDAR ( DATE ( STARTYEAR, 01, 01 ), ( DATE ( ENDYEAR, 12, 31 ) ) ),
"Period",
"P"
& VALUE (
IF (
FiscalStart <= FORMAT ( [Date], "MM" ),
(
MONTH ( [Date] ) - FiscalStart + 1
),
(
( 12 - FiscalStart + 1 )
+ MONTH ( [Date] )
)
)
),
"Fiscal Quarter",
IF (
VALUE (
IF (
FiscalStart <= FORMAT ( [Date], "MM" ),
(
MONTH ( [Date] ) - FiscalStart + 1
),
(
( 12 - FiscalStart + 1 )
+ MONTH ( [Date] )
)
)
) < 4,
"Q1",
IF (
VALUE (
IF (
FiscalStart <= FORMAT ( [Date], "MM" ),
(
MONTH ( [Date] ) - FiscalStart + 1
),
(
( 12 - FiscalStart + 1 )
+ MONTH ( [Date] )
)
)
) < 7,
"Q2",
IF (
VALUE (
IF (
FiscalStart <= FORMAT ( [Date], "MM" ),
(
MONTH ( [Date] ) - FiscalStart + 1
),
(
( 12 - FiscalStart + 1 )
+ MONTH ( [Date] )
)
)
) < 10,
"Q3",
"Q4"
)
)
),
"Fiscal Year",
IF (
FiscalStart <= FORMAT ( [Date], "MM" ),
"FY"
& ( YEAR ( [Date] ) + 1 ),
"FY" & YEAR ( [Date] )
)
)
In addition, you can refer the content in the following link to get it.
Creating a date table with a fiscal year in your Power BI data model
Best Regards
User | Count |
---|---|
18 | |
11 | |
5 | |
4 | |
3 |