Forum Discussion

amandabus21's avatar
amandabus21
Helper V
3 years ago
Solved

Months Between

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*  
Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    3 years ago

    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.           

  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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.

     

     

m_dekorte's avatar
m_dekorte
Resident Rockstar
3 years ago

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's avatar
amandabus21
Helper V
3 years ago

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's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    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's avatar
      amandabus21
      Helper V
      3 years ago

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