cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yuvarajg Member
Member

How to create a dates between from and to date

Hi

I have a From date and To date columns with in a table but i want remaining dates between from date - to date  for each and every employee id. Using calendar auto functions i have created date staticlly for all dates and also created data model connection but its not mapping as expected.

 

1.png

Regards,

Yuvaraj

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to create a dates between from and to date

Hi @yuvarajg,

Based on my test, you could refer to below steps in query editor:

Sample data:

1.PNG

Selected the two columns and use the "Unpivot columns" function:

1.PNG

Now you could get the merged columns.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User
Super User

Re: How to create a dates between from and to date

[To Date] - [From Date]

 

That will give you the number of days between the two dates.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


yuvarajg Member
Member

Re: How to create a dates between from and to date

Yes but in my report i need to create a date filter that date contains all date's. Here am expecting the date as one new column it contains all dates which have that from date and to date.

 

Regards,

Yuvaraj 

chotu27 Member
Member

Re: How to create a dates between from and to date

@yuvarajg 

 

1. Click “Edit queries” to open Power Query editor

2. Make sure your date columns are date (not date/time) format

3.  Create a custom column

4. Listofdays = { Number.From([From Date])..Number.From([ToDate])}

5. Click OK

6. Click the “diverging-arrow” icon next to “Date” and click “Expand to new rows”. Notice the addition of new rows.

7. Change the column type to “Date”

8.  Click “Close and Apply”

 

 

Final Step Create Calculated

List of days = CONCATENATEX(FILTER(ALL(Tab),Table[Emplyee ID]=EARLIER([Emplyee ID])),[Listofdays]," , ")

 

 

 

mmm.PNG

yuvarajg Member
Member

Re: How to create a dates between from and to date

unique values falls duplicate.

without duplication how can i achieve.

chotu27 Member
Member

Re: How to create a dates between from and to date

mmm2.PNG                                                                  @yuvarajg you need this view right?

Community Support Team
Community Support Team

Re: How to create a dates between from and to date

Hi @yuvarajg,

Based on my test, you could refer to below steps in query editor:

Sample data:

1.PNG

Selected the two columns and use the "Unpivot columns" function:

1.PNG

Now you could get the merged columns.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.