Forum Discussion
Months Between
Hi amandabus21 ,
You can try this method to calculate month diff between two dates.
Copy the full script into a new blank query.
let CalendarDayExample = #date(2015, 12, 22), Today = Date.From(DateTime.FixedLocalNow()), Result = (12 * Date.Year(Today) + Date.Month(Today)) - ((12 * Date.Year(CalendarDayExample )) + Date.Month(CalendarDayExample )) in ResultI hope this is helpful
Hi amandabus21 ,
Did you want to calculated the number of the months between calendar day and current day?
You can try adding a custom column in Power Query.
= Duration.Days(Date.From(DateTime.LocalNow())-[Calendar Day])/30If you don't want to get a value with decimals, you can also adjust to an integer type.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi amandabus21,
Sure just copy this bit into a new blank query
(Date as date) as number => let Today = Date.From(DateTime.FixedLocalNow()), Calc = (12 * Date.Year(Today) + Date.Month(Today)) - ((12 * Date.Year(Date)) + Date.Month(Date)) -1, Result = if Calc < 0 then 0 else Calc in ResultRename this query: fxMonthsDif
Now select the query you want to invoke it on, go to the "Add Column" tab on the ribbon and select "Invoke Custom Function"
In the dialog box, enter a new column name, select the function query and select the Date column you want to invoke it on.
Hi amandabus21
You can use Number.RoundDown
Number.RoundDown(number as nullable number, optional digits as nullable number) as nullable number
Returns the result of rounding number down to the previous highest integer. If number is null, this function returns null. If digits is provided, number is rounded to the specified number of decimal digits.
Just wrap it around the calculation, like so:
Number.RoundDown( Duration.Days(Date.From(DateTime.LocalNow())-[Calendar Day])/30 )
Thanks! Kind of extenidng this here but do you know if I can modify so that if the the previous month is not complete, it wouldnt count as 1 month.
Todays Date 5/10/23
For example with a Calenday day of 5/1/23 and greater it would give me a 0.
a calendar day of 4/1/23 - 4/30/23 would still give me a 0
(because since it hasnt been a full month from todays date, the whole month is technically not complete)
a calendar day of 3/31/23 would give me a 1
a calendar day of 2/28/23 would give me a 2 etc
Hi amandabus21
You can see if this meets your requirement
let fxMonthsDif = (Date as date) as number => let Today = Date.From(DateTime.FixedLocalNow()), Calc = (12 * Date.Year(Today) + Date.Month(Today)) - ((12 * Date.Year(Date)) + Date.Month(Date)) -1, Result = if Calc < 0 then 0 else Calc in Result, Source = Table.FromColumns( {{#date( 2023, 5, 1), #date( 2023, 4, 1), #date( 2023, 3, 30), #date( 2023, 2, 28)}}, type table [ Date = date ] ), InvokedFunction = Table.AddColumn(Source, "Months Dif", each fxMonthsDif([Date]), Int64.Type) in InvokedFunctionToday May 5th 2023 it returns this
Ps. If this helps solve your query please mark this post as Solution, thanks!
hi yes, this does work but how can I add to my original query so that it automates the dates without me having to hard code the dates?
Hi amandabus21,
Sure just copy this bit into a new blank query
(Date as date) as number => let Today = Date.From(DateTime.FixedLocalNow()), Calc = (12 * Date.Year(Today) + Date.Month(Today)) - ((12 * Date.Year(Date)) + Date.Month(Date)) -1, Result = if Calc < 0 then 0 else Calc in ResultRename this query: fxMonthsDif
Now select the query you want to invoke it on, go to the "Add Column" tab on the ribbon and select "Invoke Custom Function"
In the dialog box, enter a new column name, select the function query and select the Date column you want to invoke it on.