topic Re: How can I create custom month period (financial month period) within date table? in DAX Commands and Tips

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

JgreatX — Tue, 20 Feb 2024 17:01:10 GMT

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

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

gmsamborn — Tue, 20 Feb 2024 17:29:00 GMT

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.

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

gmsamborn — Tue, 20 Feb 2024 18:25:12 GMT

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

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

JgreatX — Wed, 21 Feb 2024 13:14:18 GMT

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

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

JgreatX — Wed, 21 Feb 2024 13:14:57 GMT

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

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

JgreatX — Wed, 21 Feb 2024 13:19:43 GMT

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

)

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

gmsamborn — Wed, 21 Feb 2024 16:43:16 GMT

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.

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

JgreatX — Mon, 26 Feb 2024 11:00:05 GMT

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

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

JgreatX — Mon, 26 Feb 2024 12:47:39 GMT

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,

Best regards

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

Dangar332 — Mon, 26 Feb 2024 14:45:18 GMT

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

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

gmsamborn — Mon, 26 Feb 2024 15:46:27 GMT

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.

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

gmsamborn — Mon, 26 Feb 2024 16:19:45 GMT

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.