Date.DatesBetween to retrieve dates between 2 dates in Power BI and Power Query

Today I’m sharing a handy function with you that allows you to retrieve all or just a couple of dates between 2 given dates: Date.DatesBetween.

Usage

This function takes 3 parameters:

  1. From- or Start-date
  2. To- or End-date
  3. A selection of ONE of these intervals: Year, Quarter, Month, Week or Day

All dates will be created at the end of the chosen interval: So if you want to analyse events with a duration for example, where you want to transform your data to show one day per (monthly) event, this function generates month-end-dates for every month within the timespan. Please not that if the To-/End-date is within a month, the last element of the list will NOT be that day, but the day of the end of that month.

The default-value for the 3rd parameter is “Day”, so if you omit the specification, the function will return a list of all days in between.

Definition


let
// ———————– Documentation ———————–
documentation_ = [
Documentation.Name = " Dates.ListDateIntervals
", Documentation.Description = " Creates a list of dates according to the chosen interval between Start and End. Allowed values for 3rd parameter: ""Year"", ""Quarter"", ""Month"", ""Week"" or ""Day"".
" , Documentation.LongDescription = " Creates a list of dates according to the chosen interval between Start and End. The dates created will always be at the end of the interval, so could be in the future if today is chosen.
", Documentation.Category = " Table
", Documentation.Source = " http://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-power-query/ .
", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com .
", Documentation.Examples = {[Description = " see http://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-power-query/ .
" , Code = "
", Result = "
"]}],
// ———————– Function Code ———————–
function_ =
(From as date, To as date, optional Selection as text ) =>
let
// Create default-value "Day" if no selection for the 3rd parameter has been made
TimeInterval = if Selection = null then "Day" else Selection,
// Table with different values for each case
CaseFunctions = #table({"Case", "LastDateInTI", "TypeOfAddedTI", "NumberOfAddedTIs"},
{ {"Day", Date.From, Date.AddDays, Number.From(To-From)+1},
{"Week", Date.EndOfWeek, Date.AddWeeks, Number.RoundUp((Number.From(To-From)+1)/7)},
{"Month", Date.EndOfMonth, Date.AddMonths, (Date.Year(To)*12+Date.Month(To))-(Date.Year(From)*12+Date.Month(From))+1},
{"Quarter", Date.EndOfQuarter, Date.AddQuarters, (Date.Year(To)*4+Date.QuarterOfYear(To))-(Date.Year(From)*4+Date.QuarterOfYear(From))+1},
{"Year", Date.EndOfYear, Date.AddYears,Date.Year(To)-Date.Year(From)+1}
} ),
// Filter table on selected case
Case = CaseFunctions{[Case = TimeInterval]},
// Create list with dates: List with number of date intervals -> Add number of intervals to From-parameter -> shift dates at the end of each respective interval
DateFunction = List.Transform({0..Case[NumberOfAddedTIs]-1}, each Function.Invoke(Case[LastDateInTI], {Function.Invoke(Case[TypeOfAddedTI], {From, _})}))
in
DateFunction,
// ———————– New Function Type ———————–
type_ = type function (
From as (type date),
To as (type date),
optional Selection as (type text meta [
Documentation.FieldCaption = "Select Date Interval",
Documentation.FieldDescription = "Select Date Interval, if nothing selected, the default value will be ""Day""",
Documentation.AllowedValues = {"Day", "Week", "Month", "Quarter", "Year"}
])
)
as table meta documentation_,
// Replace the extisting type of the function with the individually defined
Result = Value.ReplaceType(function_, type_)
in
Result

How it works

This function uses Function.Invoke to create a compact code. In step “CaseFunction” (rows 12-18), a table is created that contains all the elements for a case-selection. The first column contains the case that is selected by the 3rd parameter. The other columns contain the functions and expressions for the cases.

Table with all different elements for each case

Step “Case”  filters that table down to one remaining record:

Case = CaseFunctions{[Case = TimeInterval]},

It uses a special row-selector { [NameOfTheColumn = Condition] } that only works for columns containing unique keys.

Step “DateFunction” has the command that creates the list of dates:

DateFunction = List.Transform({0..Case[NumberOfAddedTIs]-1}, each Function.Invoke(Case[LastDateInTI], {Function.Invoke(Case[TypeOfAddedTI], {From, _})} ))

Starting with creating a list with the length of the number of intervals (yellow). The next evaluation step uses  Function.Invoke (green): This allows us to work with the function name as a variable from our Case-record. So whatever has been selected for “TypeOfAddedTI” (Time Interval) will be executed with From and the respective number from our list as its parameter (like: Date.AddWeeks(From, 2) for the third item of the list if “Week” is selected. The last evaluation-step (orange) will be Function.Invoke(CaseLastDateInTI) which shifts every returned date at the end of its Interval (here: Date.EndOfWeek).

Of course, this coding style is a matter of taste, but I found it worth sharing.

If you are not familiar with using custom functions, please check this video:
Apply PowerQuery custom Functions in your own solutions – YouTube

Enjoy & stay queryious 😉

Comments (9) Write a comment

  1. Hi
    could you please help me with the syntax the syntax doesnt work for me

    Table.AddColumn(tablename,”newnameforthemaincolumn” ,DATESBETWEEN(licencing_costs[Start],licencing[End],”month”)) it throws me error

    Reply

  2. Thank you. This is fantastic, but not quite for my use case. In my case, using historical account record data, I have a need to add rows between the row/records between ‘modified date’, which carries the records values down (fill down?) so long there is not a ‘new’ modified date for the account.

    For example, records as such:

    Here is the table example: Row#, Col1, Col2, Col3

    Starting Data/Table:
    Headers — ModifiedDate, AccountID, Value
    Row1 — 2/3/2018, Account1, $5500
    Row2 — 3/14/2018, Account3, $7600
    Row3 — 6/3/2018, Account1, $6000
    Row4 — 9/4/2018, Account1, $8000
    Row5 — 9/4/2018, Acount3, $8500

    Required Result:

    Starting Data/Table:
    Headers — ModifiedDate, AccountID, Value
    Row1 — 2/28/2018, Account1, $5500
    Row2 — 3/31/2018, Account1, $5500
    Row3 — 4/30/2018, Account1, $5500
    Row4 — 5/31/2018, Account1, $5500
    Row5 — 6/30/2018, Account1, $6000
    Row6 — 7/31/2018, Account1, $6000
    Row7 — 8/31/2018, Account1, $6000
    Row8 — 9/30/2018, Account1, $8000
    Row9 — 3/31/2018, Account3, $7600
    Row10 — 4/30/2018, Account3, $7600
    Row11 – 5/31/2018, Account3, $7600
    Row12 — 6/30/2018, Account3, $7600
    Row13 — 7/31/2018, Account3, $7600
    Row14 — 8/31/2018, Account3, $7600
    Row15 — 9/30/2018, Account3, $8500

    Any thoughts how to apply the concept of your function to get the end result I seek? (I am looking for a Power Query solution.)

    Reply

  3. Hi,
    Is there a way to expand each row to a number of rows based on the occurrence indicated in a column as presented in the table below:
    Start date End date Ocurrence code Amount required
    01 Mar 2023 31 Dec 2024 Day SER-3 1
    01 Aug 2023 31 Dec 2024 Day SER-3 2
    01 Sep 2023 31 Dec 2024 Day SER-9 2
    01 Sep 2023 31 Dec 2024 Week SER-6 2
    01 Sep 2023 31 Dec 2023 Quarter SER-7 1
    01 Sep 2023 12 Nov 2023 Month SER-1 1
    01 Oct 2023 05 Dec 2023 Month SER-2 2

    Basically every row needs to be verified with the “occurence” and expanded.
    For example for “months” and “quarter” I would need the following result:

    Start date End date Ocurrence code Amount required Monitoring
    01 Sep 2023 31 Dec 2023 Quarter SER-7 1 01 Sep 2023
    01 Sep 2023 31 Dec 2023 Quarter SER-7 1 01 Dec 2023
    01 Sep 2023 12Nov 2023 Month SER-1 1 01 Sep 2023
    01 Sep 2023 12Nov 2023 Month SER-1 1 01 Okt 2023
    01 Sep 2023 12Nov 2023 Month SER-1 1 01 Nov 2023
    01 Oct 2023 05 Dec 2023 Month SER-2 2 01 Oct 2023
    01 Oct 2023 05 Dec 2023 Month SER-2 2 01 Nov 2023
    01 Oct 2023 05 Dec 2023 Month SER-2 2 01 Dec 2023

    Many thanks in advance

    Reply

    • Hi Jonas,
      I don’t get the logic here: Why are there no Oct and Nov for SER-7 1?

      Reply

  4. Hi Imke , thanks for your reply. This is because the frequency with those subjects is quarterly. Meaning that between 01 Sep and 31 Dec there are 2 quarters as there are more than 3 months inbetween.
    Currently I am using the following code:
    “Table.AddColumn(#”Changed Type2”, “Monitor Start”, each
    if [Ocurrence] = “Day” then List.Dates([Start date], Number.From([End date]-[Start date])+1, #duration(1, 0, 0, 0))
    else if [Ocurrence] = “Week” then
    List.Dates([Start date], Number.Round(Number.From(([End date] – [Start date])/7) ,0 )+1, #duration(7, 0, 0, 0))
    else if [Ocurrence] = “Month” then
    List.Dates([Start date], Number.Round(Number.From(([End date] – [Start date])/( 365.25 / 12 )) ,0 )+1, #duration(30, 0, 0, 0))
    else if [Ocurrence] = “Year” then
    List.Dates([Start date], Number.Round(Number.From(([End date] – [Start date])/( 365.25 )) ,0 )+1, #duration(365, 0, 0, 0))
    else if [Ocurrence] = “Quarter” then
    List.Dates([Start date], Number.Round(Number.From(([End date] – [Start date])/( 365.25 /4)) ,0 )+1, #duration(90, 0, 0, 0))

    else null
    ),


    This gives me an OK result however this is not always correct for weeks, months, quarters and years

    Reply

    • Sorry, I still don’t understand.
      I would suggest to hop over to the Power BI forum and ask there. Possibly with a link to a sample file to ease understanding.

      Reply

Leave a Reply