cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Dax Help: Create table with variable # of lines according to date difference

Hi!

 

I'm having big trouble to create a new table, does anybody know the solution? it would help a Lot!!

 

I Have a table with orders like this:

OrderOriginal DateActual DateDifference
50000201/03/201809/03/20188
50000302/03/201810/03/20188
50000404/03/201810/03/20186

 

With this table, i would like to structure a new table that contais:

1- X lines for each order, beign the X the number in column "Difference" +1. So, for order 500002 i would need 9 lines repeating this same order, then 9 lines for order 500003, and 7 lines for order 500004.

2- Each line should have a different date according to its the "difference" - "line number of the  order". For instance, line 1 one order "500003" would have date 02/03/2018. Line 2 of the same order would have date 03/03/2018... up to line 8 that would have the date 10/03/2018.

 

The new table would look like this:

OrderDate
50000201/03/2018
50000202/03/2018
50000203/03/2018
50000204/03/2018
50000205/03/2018
50000206/03/2018
50000207/03/2018
50000208/03/2018
50000209/03/2018
50000302/03/2018
50000303/03/2018
50000304/03/2018
50000305/03/2018
50000306/03/2018
50000307/03/2018
50000308/03/2018
50000309/03/2018
50000310/03/2018
50000404/03/2018
50000405/03/2018
50000406/03/2018
50000407/03/2018
50000408/03/2018
50000409/03/2018
50000410/03/2018

 

Does anyone knows how to help? Thanks!!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Dax Help: Create table with variable # of lines according to date difference

@filipedonati,

 

You may refer to the DAX below.

Table =
VAR c =
    CALENDARAUTO ()
RETURN
    GENERATE (
        Table1,
        FILTER ( c, [Date] >= Table1[Original Date] && [Date] <= Table1[Actual Date] )
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Dax Help: Create table with variable # of lines according to date difference

@filipedonati,

 

You may refer to the DAX below.

Table =
VAR c =
    CALENDARAUTO ()
RETURN
    GENERATE (
        Table1,
        FILTER ( c, [Date] >= Table1[Original Date] && [Date] <= Table1[Actual Date] )
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
New Member

Re: Dax Help: Create table with variable # of lines according to date difference

Worked perfectly, thanks!

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors