Latest Enterprise DNA Initiatives

Difference between two dates using power query

Query Editor
#1

Hi there,

I want to create a column in the fact table (using power query) that returns the days between two dates. My fact table is connected with the date table using date column. Fact table has two other columns: Event_start_date and Event_end_date. I want to create column that returns the days between two dates (i.e. Event_start_date & Event_end_date) and exclude the weekends (i.e. Saturday and Sunday). Date table contains days name.

I tried something like follow:

Days diff = Table.RowCount(Table.SelectRows(dates,each(dates[date]>=[Event_start_date] and dates[date]<=[Event_end_date])))

Obviously it didnot work. Could anyone help me how to fix the issue?

#2

@leo,

I created a video on the Enterprise DNA Channel last year that walks through exactly how to do this using both Power Query and DAX. You can find it here:

I hope this is helpful.

  • Brian
#3

Hi @leo_89,

Like demonstrated by @BrianJ you can use Imke Feldmann’s custom function or enter this bit of M code in a Custom Column.

List.Count(
  List.Select(
    List.Dates( [Event_start_date], Number.From([Event_end_date] - [Event_start_date]) + 1, Duration.From(1)),
    each Date.DayOfWeek(_, 1) < 5
  )
)

And if you also want to exclude holidays (just sub in your Query/Column name reference)

List.Count(
  List.Select(
    List.Difference(
      List.Dates([Event_start_date], Number.From([Event_end_date] - [Event_start_date]) + 1, Duration.From(1)),
      List.Transform(#"Holiday Table"[Holiday Date Column], Date.From)
    ), each Date.DayOfWeek(_, 1) < 5
  )
)

From the inside out the second piece of M code does the following:

  1. create a list of dates from the [Event_start_date] until the [Event_end_date]
  2. removes all dates that are present in #“Holiday Table”[Holiday Date Column]
  3. removes all dates that are a saterday and sunday
  4. counts the number of remaining elements in the list

I hope this is helpful.

1 Like
#4

@Melissa,

This is such a good solution to such a common question, that I think when you have a chance you should put a copy of that code in the M Code Showcase, just to highlight it and make it easier for others to find in the future.

Great explanation too. Thanks!

– Brian

1 Like
#5

Thanks Melisa (@Melissa) for the reply. I tried second piece of M code but it thrown an error. I am very new in Power BI and M code. I am not sure where I made the mistake. I have attached sample file. In sample file I tried to create a column named ‘Days Diff’ in Fact Table (which is thrown an error). Could you please help me in sorting out the issue?sample3.pbix (579.4 KB)

#6

Hi @leo_89,

Your event start- and end dates are of type datetime and not of type date. The List.Dates requires a date type value as first parameter, so I’ve changed that into: Date.From([Event_start_date])

List.Count(
  List.Select(
    List.Difference(
      List.Dates(Date.From([Event_start_date]), Number.From([Event_end_date] - [Event_start_date]) + 1, Duration.From(1)),
      List.Transform(Holidays[Day], Date.From)
    ), each Date.DayOfWeek(_, 1) < 5
  )
)

Note. I’ve you don’t want to take the time portion into account you’ll have to wrap Date.From around the column references in the second parameter as well. Like so:
Number.From(Date.From([Event_end_date]) - Date.From([Event_start_date]))

I hope this is helpful.

1 Like
#7

Thanks @Melissa. It worked.