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

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.

Enjoy & stay queryious 😉

Comments (4) 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

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz