cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Magdoulin
Helper III
Helper III

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.

2 ACCEPTED SOLUTIONS
watkinnc
Super User
Super User

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

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

V-pazhen-msft
Community Support
Community Support

@Magdoulin 
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]))
Vpazhenmsft_0-1630288897029.png

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
V-pazhen-msft
Community Support
Community Support

@Magdoulin 
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]))
Vpazhenmsft_0-1630288897029.png

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

watkinnc
Super User
Super User

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

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors