cancel
Showing results for
Did you mean:
Regular Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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

Hi @Kumshan450 ,

```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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User

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

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.

Proud to be a Datanaut!

Regular Visitor

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

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.

Super User

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

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.

Proud to be a Datanaut!

Community Support Team

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

Hi @Kumshan450 ,

```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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.