Create Date Table with two custom monthly periods

[mcomsto]

Hi.  I think have a very simple question, and I'm embarrassed to ask, but I just can't seem to figure it out.

I would like to add to my existing date dimension a custom period column that creates two monthly periods where Period 1 is based on 1st thru 15th and Period 2 is based on 16th thru the last day of each month.

It would end up looking like this.  

Date, Monthly Period

1/1/2017, 2017 Jan P1

1/2/2017, 2017 Jan P1

1/3/2017, 2017 Jan P1

.

.

.

1/15/2017, 2017 Jan P1

1/16/2017, 2017 Jan P2

.

.

.

1/31/2017, 2017 Jan P2

2/1/2017, 2017 Feb P1

.

.

Etc.

Your help is greatly appreciated.

[Ashish_Mathur · Accepted solution]

Hi,

Try this Custom column formula

=FORMAT(Calendar[Date],"MMM")&" "&IF(DAY(Calendar[Date])<=15,"P1","P2")

[Anonymous]

Never be afraid to ask!  This community is very positive and always looking to help others!

I would do this in M, since calculated columns tend to bloat data models.

Assuming you have your query in M, click on ADD COLUMN....ADD CUSTOM COLUMN, then paste this formula in.

if Date.Day( [Date Column] ) <= 15 then "P1" else "P2"

Note that you may need to rename the [Date Column] to whatever it should be called.