Custom calendar table with custom start date ,end date of year ,Quarter, Periods

[SrikanthD013]

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)

[Anonymous]

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