Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to Solution.
Please try this
Months Between = IFERROR(DATEDIFF(
[date_hired],
[regularization_date],
MONTH
),-1)
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,,,,
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?
Hi Phil,
This is an example of my data.
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 )
Hi Phil,
Yes, now I'm after the number of months.
I tried the formula and i'm encountering an error:
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)
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |