Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello all,
How does one convert days into months and remaining days?
Eg: Days = 178,
Converted to Months = 5 months and 26 days
Solved! Go to Solution.
I can't think of a very elegant way to do this but this should work.
Define two new custom columns:
MonthDiff =
if Date.Day([Today]) >= Date.Day([Plan])
then 12 * (Date.Year([Today]) - Date.Year([Plan])) + Date.Month([Today]) - Date.Month([Plan])
else 12 * (Date.Year([Today]) - Date.Year([Plan])) + Date.Month([Today]) - Date.Month([Plan]) - 1
RemainderDayDiff = Duration.Days(Date.AddMonths([Today], -[MonthDiff]) - [Plan])
Hi, @niyati_61
You can try the following methods.
DAYS =
IF ( [PLAN] > [DATE TODAY], [PLAN] - [DATE TODAY], [DATE TODAY] - [PLAN] )
2. Create a new column to change the number of days to the month plus the number of days.
Column =
INT ( [DAYS] / 30 ) & "months"
& (
[DAYS]
- INT ( [DAYS] / 30 ) * 30
) & "days"
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more.
This is very similar to what I initially suggested but months don't consistently have 30 days.
Alright, that makes sense.
Okay, but what I'm doing is, I'm subtracting today's date from the Planned date (As seen in the image).
When I subtract the 2 dates in the Query Editor, I get the Number of days.
Eg: xx/xx/2021 - 31/10/2021 = -178 Days
Now, how do I convert this 178 Days into Months and remaining days? Because just 178 days doesn't make sense.
I can't think of a very elegant way to do this but this should work.
Define two new custom columns:
MonthDiff =
if Date.Day([Today]) >= Date.Day([Plan])
then 12 * (Date.Year([Today]) - Date.Year([Plan])) + Date.Month([Today]) - Date.Month([Plan])
else 12 * (Date.Year([Today]) - Date.Year([Plan])) + Date.Month([Today]) - Date.Month([Plan]) - 1
RemainderDayDiff = Duration.Days(Date.AddMonths([Today], -[MonthDiff]) - [Plan])
Hi @AlexisOlson. How would you revise this formula to handle blanks that may occur in the Planned column?
Assuming you want blanks for the result, try this:
MonthDiff =
if [Plan] = null then null
else if Date.Day([Today]) >= Date.Day([Plan])
then 12 * (Date.Year([Today]) - Date.Year([Plan])) + Date.Month([Today]) - Date.Month([Plan])
else 12 * (Date.Year([Today]) - Date.Year([Plan])) + Date.Month([Today]) - Date.Month([Plan]) - 1
If not, then replace the second "null" with whatever you do want the logic to be when [Plan] is blank.
You can't without more information (like month and day of month) unless you assume that months have a fixed number of days.
If you assume months are 30 days, then you can calculate 178/30 = 5.933 and just take the 5 part and then the remainder 178 - 5 * 30 = 178 - 150 = 28.
Months = INT ( 178 / 30 )
Days = MOD ( 178, 30 )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |