Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SrikanthD013
Regular Visitor

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

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.

PeriodQuarterFiscal YearDate
p1q1fy202206-jul-21
p1q1fy202207-jul-21
p12q4fy202202-jul-22

like this for all periods ,quarters fiscal years months(all years)

1 REPLY 1
v-yiruan-msft
Community Support
Community Support

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] )
            )
    )

yingyinr_0-1623905015827.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors