duplicating row until next date or today's date

[RMV]

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.	Type	Capacity	Date
123	A	100	20-Aug-19
123	A	100	23-Aug-19
123	A	100	25-Aug-19
123	A	100	26-Aug-19
234	B	90	20-Aug-19
234	B	90	22-Aug-19
234	B	90	25-Aug-19

The result I need is

Asset No.	Type	Capacity	Date
123	A	100	20-Aug-19
123	A	100	21-Aug-19
123	A	100	22-Aug-19
123	A	100	23-Aug-19
123	A	100	24-Aug-19
123	A	100	25-Aug-19
123	A	100	26-Aug-19
234	B	90	20-Aug-19
234	B	90	21-Aug-19
234	B	90	22-Aug-19
234	B	90	23-Aug-19
234	B	90	24-Aug-19
234	B	90	25-Aug-19
234	B	90	26-Aug-19

How can I do this in DAX or in M?

[RMV]

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

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

[AlB]

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

[RMV]

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.	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 · Accepted solution]

@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  

[RMV]

Awesome!

Thanks a lot, @AlB !

[AlB]

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] ) ) )
    )
)