Create multiple rows for dates between start and end dates in the same table for each resident no

[ankita_7]

I am joining multiple tables and getting the result where there are cono,resno, date (startdate) ,enddate and admit_disch columns. I have created a table that stores the result of this joins. I have attached a screenshot of the data sample for your reference. I have also attached screenshot of the query which I'm using to get this result.

I want cono,resno, date(startdate),enddate,admit_disch columns as it is but I want extra column for dates between startdate and enddate. Like as you can see in the query I have passed a date between start date and end date so may be that date not there in start or end date column but it is between the range of start and end date for a particular resno.

So how can we create a range of dates in a new column between start and enddate for that particular resno and cono. So that rows will replicate until enddate is reached for that particular resno. Then for the next resno again from start date to end date all range will be created in a newly created column and other all columns like resno cono will just be the same.

In this way I want to create a bridge data between two separate dates for that particular resno cono combination in a table.

Can anyone please help me with problem statement?

Thanks much in advance!

[v-jayw-msft · Accepted solution]

Hi @ankita_7 ,

In Power Bi you can use CROSSJOIN() function.

First of all you will need to create a calendar table.

CALENDAR = CALENDARAUTO()

Here's the example.

Table 2 = CROSSJOIN('Table',FILTER('CALENDAR','CALENDAR'[Date]>MIN('Table'[start date])&&'CALENDAR'[Date]<MAX('Table'[end date])))

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ankita_7]

Hi @v-jayw-msft ,

I tried this dax and it is giving me an error. I think it is coz there is a column named Date already there in the aht_census_all_table. Please check the screenshot attached below:

[v-jayw-msft]

Hi @ankita_7 ,

This error is caused by columns with the same name. Rename the Calendar[date] column will fix the error.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.