cancel
Showing results for 
Search instead for 
Did you mean: 
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
yingyinr
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors