Forum Discussion
Create Dates List from Non-Continuous Dates Column
Hello There,
I seek your assistance please guys.
I’ve a column of dates, they are not continuous dates, so, if I want to create a custom column of a list of dates that starts from the oldest date in the column Dates till the most recent date in the column Dates, how could I do that?
The current data set is just one column called Dates.
The cleanest way (rather, the way I like to do it) is to get the Min Date and Max Date as two separate values in two separate queries. Then turn them into numbers. Then make a list starting at first number and ending at last number. Finally, use Date.From to transform the list.
let
MaxDate = Number.From(List.Max(Table[DateColumn])),
MinDate =Number.From(List.Min(Table[DateColumn])),
DateList = List.Transform({MinDate..MaxDate}, Date.From)
--Nate
Anonymous
Or if you need a dax method which is also simple. lets say if you have a Table[date] column is continuous.You can just create a table.
Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
2 Replies
The cleanest way (rather, the way I like to do it) is to get the Min Date and Max Date as two separate values in two separate queries. Then turn them into numbers. Then make a list starting at first number and ending at last number. Finally, use Date.From to transform the list.
let
MaxDate = Number.From(List.Max(Table[DateColumn])),
MinDate =Number.From(List.Min(Table[DateColumn])),
DateList = List.Transform({MinDate..MaxDate}, Date.From)
--Nate
Anonymous
Or if you need a dax method which is also simple. lets say if you have a Table[date] column is continuous.You can just create a table.
Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.