Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I´m trying to create a table that contains the start date of a week and the end date of a week given a "number of periods days" and a "number of periods to be consider". For example.
Origen = 01/07/2021 (today)
numberOfPeriodsDays=7 (is equivalent a 1 week)
numberOfPeriodsToBeConsider=5 (is equivalente a 5 weeks)
This is the result I want to create with format (dd/mm/yyyy)
StartDate | EndDate |
24/05/2021 | 30/05/2021 |
31/05/2021 | 06/06/2021 |
07/06/2021 | 13/06/2021 |
14/06/2021 | 20/06/2021 |
21/06/2021 | 27/06/2021 |
28/06/2021 | 04/07/2021 |
I trying to use List.Generate but I can't make it work, perhaps I need another approach
Origen = DateTimeZone.LocalNow(),
StartDateOfPeriod= Date.StartOfWeek(Date.AddDays(DateTimeZone.LocalNow(),(-1)*numberOfPeriodsDays*numberOfPeriodsToBeConsider)),
GeneratePeriods=List.Generate(
()=> [
Counter=0,
PeriodsTable=#table
(type table
[
"StartDate"=date,
"EndDate"=date
],
{StartDateOfPeriod,Date.EndOfWeek(StartDateOfPeriod)}
)
],
each Table.Last([PeriodsTable])[EndDate]>Origen,
each [
Counter=[Counter]+1,
NewStartDateOfPeriod = Date.AddDays([StartDate],numberOfPeriodsDays),
NewEndDateOfPeriod=Date.EndOfWeek(NewStartDateOfPeriod),
PeriodsTable=Table.Combine({[PeriodsTable],Table.FromRecords({NewStartDateOfPeriod,NewEndDateOfPeriod})})
],
each [PeriodsTable]),
PeriodTable= List.Last(GeneratePeriods)
I really appreciate any help!
Thanks,
Julio
Solved! Go to Solution.
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Origin = Date.From(DateTime.LocalNow()),
DaysPerPeriod = 7,
NumberOfPeriods = 5,
InitialList = {-1*NumberOfPeriods+1 .. 0},
Custom1 = List.Transform(InitialList, each [StartDate=Date.StartOfWeek(Date.AddDays(Origin,_ * DaysPerPeriod)), EndDate = Date.EndOfWeek(Date.AddDays(Origin,_ * DaysPerPeriod)) ]),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"StartDate", "EndDate"}, {"StartDate", "EndDate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"StartDate", type date}, {"EndDate", type date}})
in
#"Changed Type"
You can tweak the Date.StartOfWeek and EndOfWeek for which day of the week you want to start/end the weeks on with Day.Saturday, etc. if needed. You can also choose the desired date format after you load the table.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Origin = Date.From(DateTime.LocalNow()),
DaysPerPeriod = 7,
NumberOfPeriods = 5,
InitialList = {-1*NumberOfPeriods+1 .. 0},
Custom1 = List.Transform(InitialList, each [StartDate=Date.StartOfWeek(Date.AddDays(Origin,_ * DaysPerPeriod)), EndDate = Date.EndOfWeek(Date.AddDays(Origin,_ * DaysPerPeriod)) ]),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"StartDate", "EndDate"}, {"StartDate", "EndDate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"StartDate", type date}, {"EndDate", type date}})
in
#"Changed Type"
You can tweak the Date.StartOfWeek and EndOfWeek for which day of the week you want to start/end the weeks on with Day.Saturday, etc. if needed. You can also choose the desired date format after you load the table.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you so much!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |