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

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 267 members 2,766 guests
Please welcome our newest community members: