DAX measure to generate rows based on two dates of a particular column

[Kumshan450]

i have  a data where i need to find out dates where employee is in a particular location. 

As data  in not in the required structure. require some methods to make it in the required structure. 

[v-yulgu-msft · Accepted solution]

Hi @Kumshan450 ,

Please new a calculated table.

Output = 
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN (
            SUMMARIZE (
                'employee travel',
                'employee travel'[Employee],
                'employee travel'[Visit],
                "startdate", MIN ( 'employee travel'[R-To date] ),
                "enddate", MAX ( 'employee travel'[R-To date] )
            ),
            CALENDAR (
                MIN ( 'employee travel'[R-To date] ),
                MAX ( 'employee travel'[R-To date] )
            )
        ),
        [Date] >= [startdate]
            && [Date] <= [enddate]
    ),
    "Employee", [Employee],
    "Visit", [Visit],
    "Date of Visit", [Date]
)

Best regards,

Yuliana Gu

[Greg_Deckler]

I would look at the GENERATE series of functions. These Quick Measures might assist:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

I would think that  you are going to need to create a new calculated table for this.

[Kumshan450]

Hi I need to create  a series of rows in a  table not dax measures. 

I have created calender table and also connected the same to my employee travel data.

Problem is the data is not in the required structure format. 

I need to make the same in the required format as mentioned in the output.

[Greg_Deckler]

Correct, and I am saying that you want to create a calculated TABLE using DAX with something like GENERATE. DAX can be used to create tables as well as measures and calculated columns. You go to Modeling | New Table and type in the formula.

You cannot insert rows into an existing table in DAX, that is now allowed. You have to create a new table that has all of the rows that you want in it from the get go.