Forum Discussion
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:
| Order | Original Date | Actual Date | Difference |
| 500002 | 01/03/2018 | 09/03/2018 | 8 |
| 500003 | 02/03/2018 | 10/03/2018 | 8 |
| 500004 | 04/03/2018 | 10/03/2018 | 6 |
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:
| Order | Date |
| 500002 | 01/03/2018 |
| 500002 | 02/03/2018 |
| 500002 | 03/03/2018 |
| 500002 | 04/03/2018 |
| 500002 | 05/03/2018 |
| 500002 | 06/03/2018 |
| 500002 | 07/03/2018 |
| 500002 | 08/03/2018 |
| 500002 | 09/03/2018 |
| 500003 | 02/03/2018 |
| 500003 | 03/03/2018 |
| 500003 | 04/03/2018 |
| 500003 | 05/03/2018 |
| 500003 | 06/03/2018 |
| 500003 | 07/03/2018 |
| 500003 | 08/03/2018 |
| 500003 | 09/03/2018 |
| 500003 | 10/03/2018 |
| 500004 | 04/03/2018 |
| 500004 | 05/03/2018 |
| 500004 | 06/03/2018 |
| 500004 | 07/03/2018 |
| 500004 | 08/03/2018 |
| 500004 | 09/03/2018 |
| 500004 | 10/03/2018 |
Does anyone knows how to help? Thanks!!
You may refer to the DAX below.
Table = VAR c = CALENDARAUTO () RETURN GENERATE ( Table1, FILTER ( c, [Date] >= Table1[Original Date] && [Date] <= Table1[Actual Date] ) )
2 Replies
You may refer to the DAX below.
Table = VAR c = CALENDARAUTO () RETURN GENERATE ( Table1, FILTER ( c, [Date] >= Table1[Original Date] && [Date] <= Table1[Actual Date] ) )Worked perfectly, thanks!
