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
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
Employee
Employee

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

 

 

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
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.