Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
filipedonati
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
v-chuncz-msft
Community Support
Community Support

@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
v-chuncz-msft
Community Support
Community Support

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

Worked perfectly, thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.