cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

duplicating row until next date or today's date

Hi guys,

 

I need help to duplicate rows with some conditions:

1. each row is duplicated until next update date

2. if the latest update date is before today's date, that latest row is duplicated until today's date.

 

The table I have is

Asset No.TypeCapacityDate
123A10020-Aug-19
123A10023-Aug-19
123A10025-Aug-19
123A10026-Aug-19
234B9020-Aug-19
234B9022-Aug-19
234B90

25-Aug-19

 

The result I need is

Asset No.TypeCapacityDate
123A10020-Aug-19
123A10021-Aug-19
123A10022-Aug-19
123A10023-Aug-19
123A10024-Aug-19
123A10025-Aug-19
123A10026-Aug-19
234B9020-Aug-19
234B9021-Aug-19
234B9022-Aug-19
234B9023-Aug-19
234B9024-Aug-19
234B9025-Aug-19
234B9026-Aug-19

 

How can I do this in DAX or in M?

1 ACCEPTED SOLUTION
Super User II
Super User II

@RMV 

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  Datanaut

View solution in original post

6 REPLIES 6
Helper V
Helper V

hi @AlB ,

 

thanks a lot!

btw, it seems this Generate function combines 2 table, if I understand it correctly (?)

so, when there's a changes on the other column, it will duplicated the data for the same date.

I didn't show it in my examples before, my bad.

how can I get the data duplicated only for a unique date?

 

here's what happened when I use the formula for my real data

result.png

 

while actually I need the other column follows the original row, 

Asset No.TypeCapacityDateConditionLocation
123A10020-Aug-19GoodLoc 1
123A10021-Aug-19GoodLoc 1
123A10022-Aug-19GoodLoc 1
123A10023-Aug-19GoodLoc 2
123A10024-Aug-19GoodLoc 2
123A10025-Aug-19AcceptableLoc 2
123A10026-Aug-19UnderutilizedLoc 2
234B9020-Aug-19AcceptableLoc 2
234B9021-Aug-19AcceptableLoc 2
234B9022-Aug-19GoodLoc 2
234B9023-Aug-19GoodLoc 2
234B9024-Aug-19GoodLoc 2
234B9025-Aug-19GoodLoc 1
234B9026-Aug-19GoodLoc 1
Super User II
Super User II

@RMV 


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

Hi @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.TypeCapacityDateConditionLocation
123A10020-Aug-19GoodLoc 1
123A10023-Aug-19GoodLoc 2
123A10025-Aug-19AcceptableLoc 2
123A10026-Aug-19UnderutilizedLoc 2
234B9020-Aug-19AcceptableLoc 2
234B9022-Aug-19GoodLoc 2
234B9025-Aug-19GoodLoc 1

 

and here is the result table.

Asset No.TypeCapacityDateConditionLocation
123A10020-Aug-19GoodLoc 1
123A10021-Aug-19GoodLoc 1
123A10022-Aug-19GoodLoc 1
123A10023-Aug-19GoodLoc 2
123A10024-Aug-19GoodLoc 2
123A10025-Aug-19AcceptableLoc 2
123A10026-Aug-19UnderutilizedLoc 2
234B9020-Aug-19AcceptableLoc 2
234B9021-Aug-19AcceptableLoc 2
234B9022-Aug-19GoodLoc 2
234B9023-Aug-19GoodLoc 2
234B9024-Aug-19GoodLoc 2
234B9025-Aug-19GoodLoc 1
234B9026-Aug-19GoodLoc 1

 

Appreciate your help

Super User II
Super User II

@RMV 

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  Datanaut

View solution in original post

Awesome!

Thanks a lot, @AlB !

Super User II
Super User II

Hi @RMV 

Try creating a new calculated table:

NewTable1 =
GENERATE (
    SUMMARIZE ( Table1; Table1[Asset No.]; Table1[Type]; Table1[Capacity] );
    GENERATESERIES (
        CALCULATE ( MIN ( Table1[Date] ) );
        MAX ( TODAY (); CALCULATE ( MAX ( Table1[Date] ) ) )
    )
)

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors