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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Hennadii
Helper IV
Helper IV

Create a table with dates based on Start / End Dates and Values on another Table

Hi there,

I have a Period table with Star Date and End Date columns. Periods are not overlap each other, so each date is related to certain Period.

PeriodStart DateEnd Date
period 101/01/202001/03/2020
period 201/07/202001/08/2020

I'd like to create a new table (like a Calendar or Date), with a columns Date and Period.

Please help me to write expression which creates the table as below.

DatePeriod
01/01/2020period 1
01/02/2020period 1
01/03/2020period 1
01/07/2020period 2
01/08/2020period 2
1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

Perhaps:

 

 

Calendar Table = 
  ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1),DATE(2020,12,31)),
    "Period",MAXX(FILTER('Table',[Start Date]<=[Date] && [End Date]>=[Date]),[Period])
  )

@Hennadii 

 



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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@Hennadii ,

Try

Create a date table
Date = calendar(Min(Period[Start Date]),Max(Period[End Date]))

 

Add a new column in that

Period = minx(filter(period, Period[Start Date]<=Date[Date] && Period[End Date]>=Date[Date]),Period[Period])

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thank you @amitchandak  and @Greg_Deckler  !!!

@amitchandak, your detailed sample helped me to understand Greg's solution which I like more as it gives a table from one expression.

Greg_Deckler
Community Champion
Community Champion

Perhaps:

 

 

Calendar Table = 
  ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1),DATE(2020,12,31)),
    "Period",MAXX(FILTER('Table',[Start Date]<=[Date] && [End Date]>=[Date]),[Period])
  )

@Hennadii 

 



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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler ,
Is there a way to do not hardcode dates in expression?

Sure, if you have dates in your data, you can use MIN and MAX or use CALENDARAUTO but not sure what your data looks like. If you have posted the sum total of your data, you will need to parse out the beginning and ending dates of your periods.

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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