How to enter end of month date to further calculate tenure

[cindymei_chiu]

Hello, 

I have several files IT provide to us on a monthly basis.  What is the best way to enter the end of month date in Power Query, and then use this end of month date to calcaulte tenure, for example Report Date 10/31/23 - Hire Date 3/28/2026 = 17.6 Tenure.  I performed this in Excel last month but I would need to create a column for the report date in order to calcuate the tenure.  I also have to create another column in Excel to factor in the Rehire Date to negate the Orig Hire Date using an if statement.  Can this be done in Power Query or Power BI?  Your assistance is greatly appreciated!  

[cindymei_chiu]

Ah...  Thank you so much, it worked!

[ChielFaber]

You could extract the name from the file name. Or another option is when you load a map you can choose to keep the creation date of the file while. By default when expanding the source files PowerQuery removes the rest of the columns. You can prevent this by changing the remove other columns step manually.

You can get the date at which your own report is created (refreshed) by using the function DateTime.FixedLocalNow for explanation of the function take a look at https://learn.microsoft.com/en-us/powerquery-m/datetime-fixedlocalnow.

You can get the end of the month from each datetime value by using the function Date.EndOfMonth. For an explanation of this function see  https://learn.microsoft.com/en-us/powerquery-m/date-endofmonth.

Date.EndOfMonth(dateTime as any) as any

For example: Date.EndOfMonth(datetime_column)   -> Date.EndOfMonth(hire_date)  

Hope this helps you find the solution.

[soniya-01]

• Know the Start Date:
• First, you need to know the date when something started, like a job or a project.
• Find the End of the Month:
• Look at a calendar and find the last day of the month when you want to calculate the tenure. It's like marking the end of a chapter.
• Enter the End of the Month Date:
• Now, take note of that last day of the month. If, for example, it's the 31st, remember that.
• Calculate Tenure:
• Once you have the start date and the end of the month date, you can calculate the tenure by counting the days or months between them.
• Example for Simple Calculation:
• Let's say you started on the 5th of a month, and you want to know your tenure at the end of that month. If the end of the month is the 31st, then your tenure is 31 - 5 = 26 days.
• Use a Calculator or Spreadsheet:
• If you have many dates to calculate, you can use a calculator or a spreadsheet program like Excel. Just subtract the start date from the end of the month date.

[edhans]

I would need to see more info, but yes, Power Query can theoritically do this. The question is where does it get the report date? Is it in the file name, or in a column of the report, or is it the last accessed/modified time of the file? The latter isn't any good to be honest as that can change.

[cindymei_chiu]

Hello, 

The date is included in the file name as such "Active_List_-_12-1-2023.XLSX" for data as of 11/30/23 but I could use 12/1/23 to calculate the tenure.  I have 3 other files for new hire, termination and transfer, and they are saved in December 2023 folder.  Each month's folder contains the same file name format for that month.  Thank you for your assistance!     

[edhans]

The following code will extract that date for you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckwuySxLjffJLC6J1403NNI11DUyMDLWi/AJjlCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"File Name" = _t]),
    AddFileDate=
        Table.AddColumn(
            Source,
            "File Date",
            each 
                Date.FromText(
                    Text.BetweenDelimiters([File Name], "_", ".", 2, 0)
                ),
            type date
        )
in
    AddFileDate

From there you can use Date.AddDays with -1 as the parameter to back it up to Nov 30, or you could do this:

Date.AddMonths(Date.EndOfMonth([File Date]), -1)

Now no matter what day it is, this will always back it up to the last day of the previous month.

[cindymei_chiu]

Hi edhans, 

Thank you, I was able to insert the file date into my query column.  However, when adding the date -1, I got this error:  

Should I not use the Custom Column?  

Thank you so much!!

[edhans · Accepted solution]

Power Query is case sensitive. It is Date.EndOfMonth(), not Date.EndofMonth()