cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kumshan450 Regular Visitor
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. 

 

Employeewisetraveldate.png

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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

1.PNG

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Kumshan450 Regular Visitor
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
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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

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

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

1.PNG

 

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.