topic Re: Create a table with dates based on Start / End Dates and Values on another Table in DAX Commands and Tips

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

Hennadii — Tue, 19 May 2020 10:48:29 GMT

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.

Period	Start Date	End Date
period 1	01/01/2020	01/03/2020
period 2	01/07/2020	01/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.

Date	Period
01/01/2020	period 1
01/02/2020	period 1
01/03/2020	period 1
01/07/2020	period 2
01/08/2020	period 2

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

Greg_Deckler — Tue, 19 May 2020 10:54:18 GMT

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

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

Hennadii — Tue, 19 May 2020 10:57:35 GMT

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

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

Greg_Deckler — Tue, 19 May 2020 11:15:11 GMT

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.

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

amitchandak — Tue, 19 May 2020 11:26:21 GMT

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])

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

Hennadii — Tue, 19 May 2020 13:17:45 GMT

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.