Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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
v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

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.

View solution in original post

7 REPLIES 7
v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

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.
chotu27
Post Patron
Post Patron

@Anonymous 

 

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

Anonymous
Not applicable

Hi @chotu27 

I tried to reproduce the steps that you mentionned il your comment
Could you please help with this error:

Custom Column.jpg

thanks in advance,

Anonymous
Not applicable

unique values falls duplicate.

without duplication how can i achieve.

mmm2.PNG                                                                  @Anonymous you need this view right?

Greg_Deckler
Super User
Super User

[To Date] - [From Date]

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.