cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TM_Visual Regular Visitor
Regular Visitor

Generating a table with values for all dates between two dates

I've got data that relates to the amount of money given to research grants, which each have a start and end date.

 

I have a table that looks like this:

ResearchGrantAward totalAward startAward end
Bridge design£1000001/01/201901/04/2019
Count bees£50000001/02/201901/05/2020
Predict the future£554534504/05/201905/12/2020

 

I would like to pivot it to create a table with a column in a date format that shows the value available per day, for all days between the start and end of each grant. I'm imagining something like this:

 

DateResearchGrantAmount per day
01/06/2019Bridge design £                      13.00
01/06/2019Count bees £                             0.52
01/06/2019Predict the future £                    2,000.10
02/06/2019Bridge design £                          13.00
02/06/2019Count bees £                             0.52

With multiple rows per date, and per research grant.

 

I know how to use Power query or DAX to calculate the number of days between the dates, the amount per day, month etc. But I don't know how to generate that table with a long list of dates, as previewed in the second table above. I would also be happy if the calculation wasn't made per day but was by week or even by month.

 

I should mention that this is fake data, and that the real data contains 19,000 grants some with a 30 year range.

Any help is much appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Generating a table with values for all dates between two dates

I need to do something similar in a report I have. In Power Query, transform the overall grant amount into a daily amount first, then use this sort of formula:

 

= Table.AddColumn(#"Some Previous Step Name", "Date", each List.Dates([Award Start], Duration.TotalDays([Award End]-[Award Start])+1, #duration(1,0,0,0)))

 

Then expand the list

4 REPLIES 4
Super User
Super User

Re: Generating a table with values for all dates between two dates

I need to do something similar in a report I have. In Power Query, transform the overall grant amount into a daily amount first, then use this sort of formula:

 

= Table.AddColumn(#"Some Previous Step Name", "Date", each List.Dates([Award Start], Duration.TotalDays([Award End]-[Award Start])+1, #duration(1,0,0,0)))

 

Then expand the list

gooranga1 Senior Member
Senior Member

Re: Generating a table with values for all dates between two dates

There is a way but it won't be quick especially as you have 19,000 grants. If the average length of grant was say 5 years that;s a total rows count of approx 7 million. Doable but it won't be quick.

 

For this you will need a date dimension. If you do not have one you can create a blank source in query editor and paste the M script at bottom of this post. This allows you to enter dates of the start and end of your date range. I didn't write it someone more skilled than me did it and kindly put it on the web.

 

When you have created this you can go back to powerbi designer slect modelling tab and create a new table using the below formula. This then will give a table of each grant and all the dates it was active.

 

SummaryTableAwards = FILTER(CROSSJOIN('Table1','Date'),Table1[Award start] <= 'Date'[Date] && Table1[Award end] >= 'Date'[Date])

Add a couple of measures

 

GrantTotal = CALCULATE(max(SummaryTableAwards[Award total]))
TotalDaysPerGrant = CALCULATE(countrows(SummaryTableAwards),ALLEXCEPT(SummaryTableAwards,SummaryTableAwards[ResearchGrant]))
GrantPerDay = [GrantTotal]/[TotalDaysPerGrant]

which gives you a table like this;

Capture.PNG

(Code for date dimension)

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),
InsertQuarter = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date]), type number),
InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]),type text),
InsertCalendarQtrOrder = Table.AddColumn(InsertCalendarQtr, "Quarter Year Order", each [Year] * 10 + [Quarter Num], type number),
InsertMonth = Table.AddColumn(InsertCalendarQtrOrder, "Month Num", each Date.Month([Date]), type number),
InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Date.ToText([Date], "MMMM", Culture), type text),
InsertMonthNameShort = Table.AddColumn(InsertMonthName, "Month Name Short", each Date.ToText([Date], "MMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthNameShort, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year]), type text),
InsertCalendarMonthOrder = Table.AddColumn(InsertCalendarMonth, "Month Year Order", each [Year] * 100 + [Month Num], type number),
InsertWeek = Table.AddColumn(InsertCalendarMonthOrder, "Week Num", each Date.WeekOfYear([Date]), type number),
InsertCalendarWk = Table.AddColumn(InsertWeek, "Week Year", each "W" & Number.ToText([Week Num]) & " " & Number.ToText([Year]), type text),
InsertCalendarWkOrder = Table.AddColumn(InsertCalendarWk, "Week Year Order", each [Year] * 100 + [Week Num], type number),
InsertWeekEnding = Table.AddColumn(InsertCalendarWkOrder, "Week Ending", each Date.EndOfWeek([Date]), type date),
InsertDay = Table.AddColumn(InsertWeekEnding, "Month Day Num", each Date.Day([Date]), type number),
InsertDayInt = Table.AddColumn(InsertDay, "Date Int", each [Year] * 10000 + [Month Num] * 100 + [Month Day Num], type number),
InsertDayWeek = Table.AddColumn(InsertDayInt, "Day Num Week", each Date.DayOfWeek([Date]) + 1, type number),
InsertDayName = Table.AddColumn(InsertDayWeek, "Day Name", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekend = Table.AddColumn(InsertDayName, "Weekend", each if [Day Num Week] = 1 then "Y" else if [Day Num Week] = 7 then "Y" else "N", type text),
InsertDayNameShort = Table.AddColumn(InsertWeekend, "Day Name Short", each Date.ToText([Date], "ddd", Culture), type text),
InsertIndex = Table.AddIndexColumn(InsertDayNameShort, "Index", 1, 1),
InsertDayOfYear = Table.AddColumn(InsertIndex, "Day of Year", each Date.DayOfYear([Date]), type number),
InsertCurrentDay = Table.AddColumn(InsertDayOfYear, "Current Day?", each Date.IsInCurrentDay([Date]), type logical),
InsertCurrentWeek = Table.AddColumn(InsertCurrentDay, "Current Week?", each Date.IsInCurrentWeek([Date]), type logical),
InsertCurrentMonth = Table.AddColumn(InsertCurrentWeek, "Current Month?", each Date.IsInCurrentMonth([Date]), type logical),
InsertCurrentQuarter = Table.AddColumn(InsertCurrentMonth, "Current Quarter?", each Date.IsInCurrentQuarter([Date]), type logical),
InsertCurrentYear = Table.AddColumn(InsertCurrentQuarter, "Current Year?", each Date.IsInCurrentYear([Date]), type logical),
InsertCompletedDay = Table.AddColumn(InsertCurrentYear, "Completed Days", each if DateTime.Date(DateTime.LocalNow()) > [Date] then "Y" else "N", type text),
InsertCompletedWeek = Table.AddColumn(InsertCompletedDay, "Completed Weeks", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.WeekOfYear(DateTime.Date(DateTime.LocalNow())) > Date.WeekOfYear([Date])) then "Y" else "N", type text),
InsertCompletedMonth = Table.AddColumn(InsertCompletedWeek, "Completed Months", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.Month(DateTime.Date(DateTime.LocalNow())) > Date.Month([Date])) then "Y" else "N", type text),
InsertCompletedQuarter = Table.AddColumn(InsertCompletedMonth, "Completed Quarters", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else if (Date.Year(DateTime.Date(DateTime.LocalNow())) < Date.Year([Date])) then "N" else if (Date.QuarterOfYear(DateTime.Date(DateTime.LocalNow())) > Date.QuarterOfYear([Date])) then "Y" else "N", type text),
InsertCompletedYear = Table.AddColumn(InsertCompletedQuarter, "Completed Years", each if (Date.Year(DateTime.Date(DateTime.LocalNow())) > Date.Year([Date])) then "Y" else "N", type text)
in
InsertCompletedYear
in
CreateDateTable
TM_Visual Regular Visitor
Regular Visitor

Re: Generating a table with values for all dates between two dates

Thank you for your help, @gooranga1 . I found a solution using the method in Power Query suggested by another user, but it's good to know that a DAX alternative is available.

TM_Visual Regular Visitor
Regular Visitor

Re: Generating a table with values for all dates between two dates

Thank you @jthomson , this is the method I used to resolve this.

 

For the reference of future users who find this post, I had a few errors popping up when I tried to do this. The error message text wasn't always clear, so I suggest (in order):

  • checking that you have no invalid dates in your start or end date columns
  • checking that no end dates were earlier than start dates
  • running the change in a new table duplicate of your original, one that doesn't have existing relationships to other tables. Power BI stopped me making changes because my 'Award ID' was previously the unique key for the table
  • checking that you haven't messed up your date table somehow