Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
cindymei_chiu
New Member

How to enter end of month date to further calculate tenure

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!  

1 ACCEPTED SOLUTION

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

8 REPLIES 8
cindymei_chiu
New Member

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

ChielFaber
Super User
Super User

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
New Member

  • 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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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!     

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

edhans_0-1701959759383.png


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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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:  

cindymei_chiu_0-1701974378460.png

Should I not use the Custom Column?  

Thank you so much!!

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors