Forum Discussion
Helper Vduplicating row until next date or today's date
Try this:
NewTable_V2 = GENERATE ( Table1; GENERATESERIES ( CALCULATE ( MIN ( Table1[Date] ) ); VAR FirstDay_ = CALCULATE ( MIN ( Table1[Date] ) ) VAR LastDay_ = CALCULATE ( MIN ( Table1[Date] ); FILTER ( ALL ( Table1[Date] ); Table1[Date] > FirstDay_ ); ALLEXCEPT ( Table1; Table1[Asset No.]; Table1[Type]; Table1[Capacity] ) ) RETURN IF ( ISBLANK ( LastDay_ ); TODAY (); LastDay_ - 1 ) ) )It fills up the last one up to today as you described initially. The new date column will be shown as "Value" and the old Date column is shown as well. You can use SELECTCOLUMNS to remove the old one and change the name of the new one.
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
Community Champion
while actually I need the other column follows the original row,
Asset No. Type Capacity Date Condition Location 123 A 100 20-Aug-19 Good Loc 1 123 A 100 21-Aug-19 Good Loc 1 123 A 100 22-Aug-19 Good Loc 1 123 A 100 23-Aug-19 Good Loc 2 123 A 100 24-Aug-19 Good Loc 2 123 A 100 25-Aug-19 Acceptable Loc 2 123 A 100 26-Aug-19 Underutilized Loc 2 234 B 90 20-Aug-19 Acceptable Loc 2 234 B 90 21-Aug-19 Acceptable Loc 2 234 B 90 22-Aug-19 Good Loc 2 234 B 90 23-Aug-19 Good Loc 2 234 B 90 24-Aug-19 Good Loc 2 234 B 90 25-Aug-19 Good Loc 1 234 B 90 26-Aug-19 Good Loc 1
what is this, the initial or the final table? I obviously need both and an explanation on how Location and Condition are calculated in the final table. Otherwise I have to be guessing and we waste time...
Helper VHi AlB ,
sorry for making it unclear.
Location and Condition column is just a value of an asset at a date.
The original table I have is like an update log of each asset at a date; any changes of the asset's property at a date is updated in this table.
The result I'm trying to have is showing each asset's property each day until today's date. If there's no log for an asset at a date, it simply duplicate the value of the previous update log.
This is the original table
| Asset No. | Type | Capacity | Date | Condition | Location |
| 123 | A | 100 | 20-Aug-19 | Good | Loc 1 |
| 123 | A | 100 | 23-Aug-19 | Good | Loc 2 |
| 123 | A | 100 | 25-Aug-19 | Acceptable | Loc 2 |
| 123 | A | 100 | 26-Aug-19 | Underutilized | Loc 2 |
| 234 | B | 90 | 20-Aug-19 | Acceptable | Loc 2 |
| 234 | B | 90 | 22-Aug-19 | Good | Loc 2 |
| 234 | B | 90 | 25-Aug-19 | Good | Loc 1 |
and here is the result table.
| Asset No. | Type | Capacity | Date | Condition | Location |
| 123 | A | 100 | 20-Aug-19 | Good | Loc 1 |
| 123 | A | 100 | 21-Aug-19 | Good | Loc 1 |
| 123 | A | 100 | 22-Aug-19 | Good | Loc 1 |
| 123 | A | 100 | 23-Aug-19 | Good | Loc 2 |
| 123 | A | 100 | 24-Aug-19 | Good | Loc 2 |
| 123 | A | 100 | 25-Aug-19 | Acceptable | Loc 2 |
| 123 | A | 100 | 26-Aug-19 | Underutilized | Loc 2 |
| 234 | B | 90 | 20-Aug-19 | Acceptable | Loc 2 |
| 234 | B | 90 | 21-Aug-19 | Acceptable | Loc 2 |
| 234 | B | 90 | 22-Aug-19 | Good | Loc 2 |
| 234 | B | 90 | 23-Aug-19 | Good | Loc 2 |
| 234 | B | 90 | 24-Aug-19 | Good | Loc 2 |
| 234 | B | 90 | 25-Aug-19 | Good | Loc 1 |
| 234 | B | 90 | 26-Aug-19 | Good | Loc 1 |
Appreciate your help
- AlB
Try this:
NewTable_V2 = GENERATE ( Table1; GENERATESERIES ( CALCULATE ( MIN ( Table1[Date] ) ); VAR FirstDay_ = CALCULATE ( MIN ( Table1[Date] ) ) VAR LastDay_ = CALCULATE ( MIN ( Table1[Date] ); FILTER ( ALL ( Table1[Date] ); Table1[Date] > FirstDay_ ); ALLEXCEPT ( Table1; Table1[Asset No.]; Table1[Type]; Table1[Capacity] ) ) RETURN IF ( ISBLANK ( LastDay_ ); TODAY (); LastDay_ - 1 ) ) )It fills up the last one up to today as you described initially. The new date column will be shown as "Value" and the old Date column is shown as well. You can use SELECTCOLUMNS to remove the old one and change the name of the new one.
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers