Wednesday, September 18, 2024

Create a Due Date based on a Date


Yet another Power Query note for Power BI. 

I have a string of dates in which I need to create a due date as the 5th of the following month.

In Power Query,
  1. Choose the Date Column
    1. Choose Add Column Tab
      1. From Date & Time
        1. Date > Month > Month
  2. Go to the formula box and change
    1. "Month" to "NextMonthDate"
    2. "Date.Month([Date]" to "Date.AddMonths([Date],1"
  3. Change NextMonthDate column to a Date type, if is not already
  4. Choose the NextMonthDate Column
    1. Choose Add Column Tab
      1. From Date & Time
        1. Date > Month > Month
  5. Add Column Tab
    1. Custom Column
      1. Name = Fifth
        1. = 5
  6. Choose the NextMonthDate Column
    1. Choose Add Column Tab
      1. From Date & Time
        1. Date > Year > YEar
  7. Select Columns for Month, Fifth, Year
    1. Transform to Text type
  8. Add Column
    1. Custom Column
      1. Name = "DueDate"
      2. [Month]&"/"&[Fifth]&"/"&[Year]
  9. Select DueDate Column
    1. Transform to Date type

There may be a more effective way, but this method seems to work perfectly.

1 comment:

  1. Other methods ...

    https://community.fabric.microsoft.com/t5/Power-Query/Create-a-specific-Due-Date-based-on-another-Date/m-p/4157719#M136590

    ReplyDelete

Popular Variations