cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
miguelflores
Advocate I
Advocate I

How to get month in a date

Hi!

 

I'm encountering a road block, how do i get the month in a date data type?

I have a column date hired and regularization date. I want to compute for the 5, 3 and 1 month remaining before regularization.

I'm also thinking if the regularization date is on the next year of the date hired, how do i compute for the month/s remaining before regularization date? 

 

Help Please,

1 ACCEPTED SOLUTION

Please try this

 

Months Between = IFERROR(DATEDIFF(
[date_hired],
[regularization_date],
MONTH
),-1)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
synergised
Resolver II
Resolver II

you can use the dax formulas

 

https://msdn.microsoft.com/en-us/library/dn802538.aspx

 

 

Month Diff = DATEDIFF(MIN( 'View_EntryDate'[Entry Quarter] ), MAX( 'View_EntryDate'[Entry Month]), month )

 

Month()

Year()

etc,,,,

Phil_Seamark
Microsoft
Microsoft

Hi @miguelflores,

 

If I understand your question correctly, you have a column that represents months and you can't convert this to a data type of Date.

 

Try making it a single day of the month (include day month and year).  I suggest making it the 1st of the month.

 

Do you have an example of the data that won't convert?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phil,

 

This is an example of my data.

Capture.PNG

 

My goal is to get how many months remaining before regularization.

 

Thanks,

Hi @miguelflores,

 

It looks like your columns are using a DATE datatype whcih is good.

 

If you are just after the difference between the two dates on each row you can add a calculated column to your table along these lines.  This will count the number of months between the two dates.  Please let me know if this is now what you are after.

 

Months Between = DATEDIFF(
                        [date_hired],
                        [regularization_date],
                        MONTH
                        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phil,

 

Yes, now I'm after the number of months.

I tried the formula and i'm encountering an error:

Capture.PNG

 

I also tried switching the regularization_date and hired_date

Months Between = DATEDIFF([date_hired],[regularization_date],MONTH)
Months Between = DATEDIFF([regularization_date],[date_hired]MONTH)

 

Thanks!

Please try this

 

Months Between = IFERROR(DATEDIFF(
[date_hired],
[regularization_date],
MONTH
),-1)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks Phil! I got it! haha

 

 

Sean
Community Champion
Community Champion

Are all months going to represent dates in the same year?

Not the dates you'll calculate but the months you have to transform into dates are they all in the same year?

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors