cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
chris7990 Occasional Visitor
Occasional Visitor

How to pick out a specific value in a date field

Hey,

 

I want to pick out the month value out of a date value. The format of the date is DD/MM/YYYY.

 

When I've pulled out the month value I want to put it into a custom column with "Qtr1", "Qtr2" e.t.c. depending on the month

 

if [Monthly Reference Line] CONTAINS 10,11,12 then "Qtr 4"

 

Above is what I've tried so far

2 ACCEPTED SOLUTIONS

Accepted Solutions
ValentinBIA Regular Visitor
Regular Visitor

Re: How to pick out a specific value in a date field

Hi @chris7990,

 

If you don't mind using the power query editor instead of going through DAX, then:

- Open your Query in the PQ Editor

- Make sure your date column is a Type: Date, if not, then change the type to date

- Select your date column

- On the upper ribbon, click Add Column, then Date, then Quarter, then Quarter of the year

 

This should create a new column based on the quarter of your date.

 

Hope that helps

Best,

Valentin

Did I answer your question? Mark my post as a solution!

Community Support Team
Community Support Team

Re: How to pick out a specific value in a date field

Hi@chris7990

You can try to use formula as below in Edit Queries:

 

Month No=Date.Month([Monthly Reference Line])

Quarter =Date.QuarterOfYear([Monthly Reference Line])

 

Of course, you can also use DAX function to add calculate column as:

 

Month No = Month([Monthly Reference Line])

Quarter = "qtr" & INT ( FORMAT ( [Monthly Reference Line], "q") )

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
ValentinBIA Regular Visitor
Regular Visitor

Re: How to pick out a specific value in a date field

Hi @chris7990,

 

If you don't mind using the power query editor instead of going through DAX, then:

- Open your Query in the PQ Editor

- Make sure your date column is a Type: Date, if not, then change the type to date

- Select your date column

- On the upper ribbon, click Add Column, then Date, then Quarter, then Quarter of the year

 

This should create a new column based on the quarter of your date.

 

Hope that helps

Best,

Valentin

Did I answer your question? Mark my post as a solution!

Community Support Team
Community Support Team

Re: How to pick out a specific value in a date field

Hi@chris7990

You can try to use formula as below in Edit Queries:

 

Month No=Date.Month([Monthly Reference Line])

Quarter =Date.QuarterOfYear([Monthly Reference Line])

 

Of course, you can also use DAX function to add calculate column as:

 

Month No = Month([Monthly Reference Line])

Quarter = "qtr" & INT ( FORMAT ( [Monthly Reference Line], "q") )

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 116 members 1,664 guests
Please welcome our newest community members: