Forum Discussion

brunomoriya's avatar
brunomoriya
Helper I
8 years ago
Solved

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
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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

  • Michal_cwiok's avatar
    Michal_cwiok
    Resolver II
    8 years ago

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

     

    Before:

     

    Select the last column and click on Fill and down:

     

    What I get:

     

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

     

    Thanks

    • Michal_cwiok's avatar
      Michal_cwiok
      Resolver II
      8 years ago

      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:

       

       

      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:

      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:

       

      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

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

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