cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brunomoriya Frequent Visitor
Frequent Visitor

Repeat last date for each row

Hello everyone,

 

As you can see, i need a calculated column, that repeats the last date whenever the value between "Total Left" and "Amount Left Day Before" doesnt change, and if i get a difference i get the present day.

 

So, for "Date" 03/03/18 i got 34 and 0.... so it returns 03/03/2018. The next day i still got 34 and 34, so it keeps the date 03/03/18.

However on date 03/06/18 i got 140 and 34, so the "Last Movement Day" receive 03/06/18.

 

I did a calculated column, but i cant keep the previous day ( i got a blank value ) when "amounts" column keeps equals. Can someone help me filling blank cells with present date values?

 

Many thanks !!

 

    ( i need that columm )(best what i got )
DateProductTotal LeftAmount Left Day BeforeLast Movement DayLast Movement Day
03/03/2018Water34003/03/201803/03/2018
03/04/2018Water343403/03/2018(blank)
03/05/2018Water343403/03/2018(blank)
03/06/2018Water1403403/06/201803/06/2018
03/07/2018Water16014003/07/201803/07/2018
03/08/2018Water16016003/07/2018(blank)
03/09/2018Water16016003/07/2018(blank)
03/10/2018Water23016003/10/201803/10/2018
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Repeat last date for each row

@brunomoriya

 

here is a dax calculated column

 

Column =
VAR Dif = Table1[Total Left] - Table1[Amount Left Day Before]
VAR previousrow =
    TOPN (
        1,
        FILTER (
            Table1,
            Table1[Product] = EARLIER ( Table1[Product] )
                && Table1[Date] < EARLIER ( Table1[Date] )
                && ( Table1[Total Left] - Table1[Amount Left Day Before] )
                <> 0
        ),
        [Date], DESC
    )
RETURN
    IF ( Dif = 0, MINX ( previousrow, [Date] ), Table1[Date] )
4 REPLIES 4
Michal_cwiok Regular Visitor
Regular Visitor

Re: Repeat last date for each row

What I would suggest in your case is simply apply "Fill down" function.

 

Before:

before.png

 

Select the last column and click on Fill and down:

filldown.png

 

What I get:

filldown.png

 

Let me know if this is the solution you are after. If not, we will work it out in Power Query - no problem Smiley Happy

 

Thanks

Highlighted
Michal_cwiok Regular Visitor
Regular Visitor

Re: Repeat last date for each row

Ok, I think I might have jumped to gun here. I see that you are using a calculated column, so Fill Down function will not work.

My result:

filldown.png

 

 

The code I have used:

let
    Source = Excel.Workbook(File.Contents("C:\PowerBI_problem.xlsx"), null, true),
    RFC_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(RFC_Sheet, [PromoteAllScalars=true]),
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1),
    New_app = List.Accumulate(#"Added Index"[Index],{0}, (state,current) =>  if current = 0 then {#"Added Index"{0}[Date]} else if #"Added Index"{current-1}[Total Left] = #"Added Index"{current}[Total Left] then state & {List.Last(state)} else state & {#"Added Index"{current}[Date]}),
    Add_columns= Table.FromColumns(Table.ToColumns(#"Added Index")&{New_app}),
    Column_rename = Table.FromColumns({Table.ColumnNames(Add_columns)}&{Table.ColumnNames(#"Added Index")&{"Last Movement Day"}},{"Old","New"}),
    //Convert each row to a list
    Column_rename_torows = Table.ToRows(Column_rename),
    //Rename it using the list
    Renamed_cols = Table.RenameColumns(Add_columns, Column_rename_torows )

      in
     Renamed_cols

 

I have pasted your table into Excel and then imported into Power BI into Power Query:

filldown.png

Then I add Index column:

   

 #"Added Index" = Table.AddIndexColumn(#"Removed Bottom Rows", "Index", 0, 1)

 

Then I add this crazy line, which solves your problem:

 New_app = List.Accumulate(#"Added Index"[Index],{0}, (state,current) =>  if current = 0 then {#"Added Index"{0}[Date]} else if #"Added Index"{current-1}[Total Left] = #"Added Index"{current}[Total Left] then state & {List.Last(state)} else state & {#"Added Index"{current}[Date]}),
    

I have explained it here.  The effect is the list of values you are after:

filldown.png

 

The rest of the steps involves pasting it all together and renaming the columns:

    Add_columns= Table.FromColumns(Table.ToColumns(#"Added Index")&{New_app}),
    Column_rename = Table.FromColumns({Table.ColumnNames(Add_columns)}&{Table.ColumnNames(#"Added Index")&{"Last Movement Day"}},{"Old","New"}),
    //Convert each row to a list
    Column_rename_torows = Table.ToRows(Column_rename),
    //Rename it using the list
    Renamed_cols = Table.RenameColumns(Add_columns, Column_rename_torows )

Let me know if it helped.

 

Thanks

Super User
Super User

Re: Repeat last date for each row

@brunomoriya

 

here is a dax calculated column

 

Column =
VAR Dif = Table1[Total Left] - Table1[Amount Left Day Before]
VAR previousrow =
    TOPN (
        1,
        FILTER (
            Table1,
            Table1[Product] = EARLIER ( Table1[Product] )
                && Table1[Date] < EARLIER ( Table1[Date] )
                && ( Table1[Total Left] - Table1[Amount Left Day Before] )
                <> 0
        ),
        [Date], DESC
    )
RETURN
    IF ( Dif = 0, MINX ( previousrow, [Date] ), Table1[Date] )
brunomoriya Frequent Visitor
Frequent Visitor

Re: Repeat last date for each row

@Zubair_Muhammad

 

Thanks a lot !! This worked !!

 

@Michal_cwiok

 

Thanks for helping, but a dax solution is a better way for me!!