Forum Discussion
Add new rows based on previous and next row
Hello Power BI community,
I need a help creating a new row based on previous and next row. I currently have a table like this:
| Account | Pay Date |
| A | 01/01/2020 |
| A | 02/01/2020 |
| A | 03/01/2020 |
| A | 05/01/2020 |
| B | 08/01/2019 |
| B | 09/01/2019 |
| B | 10/01/2019 |
| B | 11/01/2019 |
| B | 01/01/2020 |
I want to make a new row and add a month if date is not continuous based on the previous and next row. For example, 04/01/2020 should be added for account A and 12/01/2019 for acccount B. Some accounts have all the rows I need, so I don't need to add rows for them.
This is what it should look like:
| Account | Pay Date |
| A | 01/01/2020 |
| A | 02/01/2020 |
| A | 03/01/2020 |
| A | 04/01/2020 |
| A | 05/01/2020 |
| B | 08/01/2019 |
| B | 09/01/2019 |
| B | 10/01/2019 |
| B | 11/01/2019 |
| B | 12/01/2019 |
| B | 01/01/2020 |
How can I acieve this?
2 Replies
- VahidDM
Super User
HI Anonymous
I think the best solution for you is to use a calendar date and relate that to your table:
https://www.vahiddm.com/post/creating-calendar-table-with-3-stepsbut if you need DAX to create a new table and add missing dates, use this code to add a new table with DAX:
Table 2 = VAR _A = FILTER ( CALCULATETABLE ( CALENDAR ( MIN ( 'Table'[Pay Date] ), MAX ( 'Table'[Pay Date] ) ), ALLEXCEPT ( 'Table', 'Table'[Account] ) ), DAY ( [Date] ) = 1 ) VAR _B = VALUES ( 'Table'[Account] ) RETURN FILTER ( CROSSJOIN ( _B, _A ), [Date] >= CALCULATE ( MIN ( 'Table'[Pay Date] ), 'Table'[Account] = EARLIER ( [Account] ) ) && [Date] <= CALCULATE ( MAX ( 'Table'[Pay Date] ), 'Table'[Account] = EARLIER ( [Account] ) ) )Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
