How to create a column for a customize month

[PBI_newuser]

Hi, how to create a Month column if the date is N-10 days prior current month and N-10 days prior next month.

For example: 

Month	Start Date (N-10 days prior current month)	End Date (N-10 days prior next month)
July	21st June	21st July
August	22nd July	21st August
September	22nd August	20th September

[amitchandak]

@PBI_newuser , If you have date

Then

Start Date  = eomonth([Date],0) -10

End Date  = eomonth([Date],1) -10

If you have month name create a date fist like

Date = "01-" & [Month] & "-" & [Year] // you can change data type to date

[PBI_newuser]

Hi @amitchandak , based on the start and end date, how to create the month column?

I have date field in my table.

Month	Start Date (N-10 days prior current month)	End Date (N-10 days prior next month)
2021-07	21-6-2021	21-7-2021
2021-08	22-7-2021	21-8- 2021
2021-09	22-8-2021	20-9-2021

[mahoneypat]

You can create a DAX column with an expression like this.  Replace Table with your actual table name.

MonthColumn = FORMAT(Table[End Date], "yyyy-mm")

Pat

[PBI_newuser]

Hi @mahoneypat , actually I wanna group the date by month to calculate the some metrics.

For example, I want to calculate the performance of each team in each month.

If the date falls between start date and end date, then take the month of end date.

Date	Month
25-6-2021	2021-07
15-7-2021	2021-07
22-7-2021	2021-08
10-8-2021	2021-08
31-8-2021	2021-09
20-9-2021	2021-09
21-9-2021	2021-10

[mahoneypat · Accepted solution]

Please try this column expression instead.

MonthColumn =
VAR thisdate = 'Date'[Date]
VAR daysfromend =
    INT ( EOMONTH ( thisdate, 0 ) - thisdate )
VAR monthtoformat =
    IF ( daysfromend <= 10, EOMONTH ( thisdate, 1 ), thisdate )
RETURN
    FORMAT ( monthtoformat, "yyyy-mm" )

Pat

[PBI_newuser]

Thanks @mahoneypat ! It works!