How can I create custom month period (financial month period) within date table?

[JgreatX]

Hi all,

I am trying to get an answer googling around but could not find it so was hoping for your help.

I have a date table with calendar and fiscal years and months.

Anyway I would also need to create new column within the date table called "FinancialMonth". Financial months are not starting from 1st of the month and ending 31st of the month but instead are as follows:

• Jan: 16th Dec previous month till 15th Jan
• Feb: 16th Jan till 15th Feb
• Mar: 16th Feb till 15th Mar
• Apr: 16th Mar till 15 Apr
• May: 16 Apr till 15 May
• Jun: 16 May till 15 Jun
• Jul: 16 Jun till 15 Jul
• Aug: 16 Jul till 15 Aug
• Sep: 16 Aug till 15 Sep
• Oct: 16 Sep till 15 Oct
• Nov: 16 Oct till 15 Nov
• Dec: 16 Nov till 15 Dec

Thank you a lot for your help.

Best regards

[gmsamborn]

@JgreatX 

If I had known you were going to be formatting it as a date, I might have tried something like this.

_Fiscal Period 2 = 
VAR _Start = EOMONTH( [Date], -2 ) + 16
VAR _End = EOMONTH( [Date], -1 ) + 15
VAR _Next = EOMONTH( [Date], 0 ) + 1
VAR _Prev = EOMONTH( [Date], -1 ) + 1
VAR _Result =
    IF(
        [Date] >= _Start
            && [Date] <= _End,
        _Prev,
        _Next
    )

RETURN
    _Result

Again, since it returns a date instead of a string, it will sort automatically.

[Dangar332]

Hi, @JgreatX 

i have a question 
feb contain 28(29 sometime) days  so how feb fit 30 days in 28 days?

as your requirnment 

• Feb: 16th Jan till 15th Feb

[gmsamborn]

Hi @JgreatX 

Would a calculated column (in your Date table) like this help?

Fiscal Period = 
VAR _Start = EOMONTH( [Date], -2 ) + 16
VAR _End = EOMONTH( [Date], -1 ) + 15
VAR _StYear = YEAR( _Start )
VAR _Text1 = FORMAT( _Start, "mmmm" ) & " " & _StYear
VAR _Text2 = FORMAT( _End, "mmmm" ) & " " & [Year]
VAR _Result =
    IF(
        [Date] >= _Start
            && [Date] <= _End,
        _Text1,
        _Text2
    )

RETURN
    _Result

Let me know if you have any questions.

[JgreatX]

Thanks a lot,

I think I am very close now, the only issue now i see is when i want to present results within a fiscal year. I was wondering if you could help me adjust the code so that Financial Month period would be as follows (to fit fiscal year reporting that is from 1st July to 30th June)

Financial Month period (adjusted):

• Jan: 16th Dec previous month till 15th Jan
• Feb: 16th Jan till 15th Feb
• Mar: 16th Feb till 15th Mar
• Apr: 16th Mar till 15 Apr
• May: 16 Apr till 15 May
• Jun: 16 May till 30 Jun (adjusted due to fiscal year end)
• Jul: 1 Jul till 15 Jul (adjusted due to fiscal year start)
• Aug: 16 Jul till 15 Aug
• Sep: 16 Aug till 15 Sep
• Oct: 16 Sep till 15 Oct
• Nov: 16 Oct till 15 Nov
• Dec: 16 Nov till 15 Dec

Thank you a lot in advance.

Best regards

[JgreatX]

Just fyi my calendar table looks like this now:

Calendar =

--Inputs--

VAR WeekStartsOn = "Mon"

VAR FiscalStartMonth = 7

--NOTE: Calendar week starts from Monday

--Calculation--

RETURN

    ADDCOLUMNS (

        CALENDARAUTO ( FiscalStartMonth - 1 ),

        "MIndex", MONTH ( [Date] ),

        "FiscalMIndex", MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ),

        "CalMonth", FORMAT ( [Date], "mmm", "en-GB" ),

        "CalQtr", "Q"

            & CEILING ( MONTH ( [Date] ), FiscalStartMonth - 1 ) / ( FiscalStartMonth - 1 ),

        "CalYear", YEAR ( [Date] ),

        "Fiscal Week",

        VAR FiscalFirstDay =

            IF (

                MONTH ( [Date] ) < FiscalStartMonth,

                DATE ( YEAR ( [Date] ) - 1, FiscalStartMonth, 1 ),

                DATE ( YEAR ( [Date] ), FiscalStartMonth, 1 )

            )

        VAR FilteredTableCount =

            COUNTROWS (

                FILTER (

                    SELECTCOLUMNS ( GENERATESERIES ( FiscalFirstDay, [Date] ), "Dates", [Value] ),

                    FORMAT ( [Dates], "ddd" ) = WeekStartsOn

                )

            )

        VAR WeekNos =

            IF (

                FORMAT ( FiscalFirstDay, "ddd" ) <> WeekStartsOn,

                FilteredTableCount + 1,

                FilteredTableCount

            )

        RETURN

            "Week " & WeekNos,

        "Fiscal Qtr", "Q"

            & CEILING ( MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ), 3 ) / 3,

        "Fiscal Year",

        VAR CY =

            RIGHT ( YEAR ( [Date] ), 2 )

        VAR NY =

            RIGHT ( YEAR ( [Date] ) + 1, 2 )

        VAR FinYear =

            IF ( MONTH ( [Date] ) > ( FiscalStartMonth - 1 ), "FY" & NY, "FY" & CY )

        RETURN

            FinYear,

        "CalWeekNo", WEEKNUM ( [Date], 2 ),

        "Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ),

        "Day", FORMAT ( [Date], "ddd" ),

        "CustomDate", FORMAT ( [Date], "d/mm" ),

        "FinancialMonth2",

        VAR _Start = EOMONTH( [Date], -2 ) + 16

        VAR _End = EOMONTH( [Date], -1 ) + 15

        VAR _StYear = YEAR( _Start )

        VAR _Text1 = FORMAT( _Start, "mmmm", "en-GB") & " " & _StYear

        VAR _Text2 = FORMAT( _End, "mmmm", "en-GB") & " " & (YEAR ( [Date] ))

        VAR _Result =

            IF(

                [Date] >= _Start

                    && [Date] <= _End,

                _Text1,

                _Text2

            )

        RETURN

            _Result,

        "FinancialMonth3",

        VAR _Start = EOMONTH( [Date], -2 ) + 16

        VAR _End = EOMONTH( [Date], -1 ) + 15

        VAR _StYear = YEAR( _Start )

        VAR _Text1 = FORMAT( _Start, "mmm", "en-GB")

        VAR _Text2 = FORMAT( _End, "mmm", "en-GB")

        VAR _Result =

            IF(

                [Date] >= _Start

                    && [Date] <= _End,

                _Text1,

                _Text2

            )

        RETURN

            _Result

    )

[gmsamborn]

Hi @JgreatX 

If you want, those columns can be added as calculated columns instead of including the code for those columns in the create table statement.

It's a matter of personal preference but I think having them separate makes the code look cleaner.

[JgreatX]

Thanks mgsamborn a lot for your help,

Just fyi in order for Financial Month period to show correctly as per my innitial instructions above i needed to slightly twick the code. Maybe is not the cleanest one but it is working:

Finance Period=

        VAR _Start = EOMONTH( [Date], -2 ) + 16

        VAR _End = EOMONTH( [Date], -1 ) + 15

        VAR _FinanceMonthOn16 = EOMONTH( [Date], 0 ) + 1 //If date falls on 16th of August or after formula below assignts the Finance period to Next month (September)

        VAR _FinanMonthUntil15 = EOMONTH( [Date], -1 ) + 1 // If date falls to 15th of August or earlier formula below assigns the period to current Month (August)

        VAR _StYear = YEAR( _Start )

        VAR _EnYear = YEAR ( _FinanceMonthOn16 )

        VAR _Text1b = FORMAT( _FinanceMonthOn16, "d/mm", "en-GB") & "/" & _EnYear

        VAR _Text2b = FORMAT( _FinanMonthUntil15, "d/mm", "en-GB") & "/" & YEAR ( [Date] )

        VAR _Result =

            IF(

                [Date] >= _Start

                    && [Date] <= _End,

                _Text2b,

                _Text1b

            )

        RETURN

            _Result

[JgreatX]

Hi mgsamborn,

Would you be able to give me a tip how to write sort column based on the latest formula above.

Thanks a lot,

BR

Thanks a lot,

Best regards

[gmsamborn]

Hi @JgreatX 

It looks like your column is returning a date in text format.  If you change the Data type to Date (in Column Tools, not the column definition), it should sort itself regardless of format chosen.

Here is my example.  (My sorting is different.)

Custom Fiscal Month.pbix

Also, the reason I had a separate sort column was because Jan-24, Feb-24, Mar-24 don't automatically sort.  You don't have that problem.

Let me know if you have any questions.

[gmsamborn]

You would probably need a column to sort by.

Sort Fiscal Period = 
VAR _Start = EOMONTH( [Date], -2 ) + 16
VAR _End = EOMONTH( [Date], -1 ) + 15
VAR _Next = EOMONTH( [Date], 0 ) + 1
VAR _Var1 = ( YEAR( _Next ) * 100 ) + MONTH( _Next )
VAR _Var2 = ( [Year] * 100 ) + MONTH( _End )
VAR _Result =
    IF(
        [Date] >= _Start
            && [Date] <= _End,
        _Var2,
        _Var1
    )
RETURN
    _Result

[JgreatX]

Not sure what would that column be but above solution looked very close what i am looking for, thanks again