Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mcomsto
Helper I
Helper I

Create Date Table with two custom monthly periods

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.

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

Try this Custom column formula

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

 

Try this Custom column formula

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.