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:
- From- or Start-date
- To- or End-date
- 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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 😉
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
What does the error message say?
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.)
Hi Marc,
you create a the required column using this technique: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/
/Imke
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
Hi Jonas,
I don’t get the logic here: Why are there no Oct and Nov for SER-7 1?
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
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.
Hi, how can I make the week intervals end in sunday instead of saturday?