Months Between

[amandabus21]

How can I find the months between the Calendar Day and today. 

This should be for each Material.Material level  01 key.

*Power Query Preferred*

[m_dekorte · Accepted solution]

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
  Result

I hope this is helpful

[v-stephen-msft · Accepted solution]

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])/30

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

[v-stephen-msft · Accepted solution]

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])/30

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

[amandabus21]

Is there a way I can make it only round down unless its the next full number?

so 0.97 would be "0"

but 1.97 would be "1"  etc.

[m_dekorte]

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 )

[amandabus21]

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

[m_dekorte]

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
    InvokedFunction

Today  May 5th 2023 it returns this

Ps. If this helps solve your query please mark this post as Solution, thanks!

[amandabus21]

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?

[m_dekorte · Accepted solution]

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
        Result

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

[amandabus21]

this is perfect, thank you so much!!!!

[m_dekorte · Accepted solution]

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
  Result

I hope this is helpful