Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!
Solved! Go to Solution.
You may refer to the DAX below.
Table = VAR c = CALENDARAUTO () RETURN GENERATE ( Table1, FILTER ( c, [Date] >= Table1[Original Date] && [Date] <= Table1[Actual Date] ) )
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!
User | Count |
---|---|
15 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
16 | |
11 | |
5 | |
4 | |
3 |