Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
brunomoriya
Helper I
Helper I

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

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

Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
Michal_cwiok
Resolver II
Resolver II

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 🙂

 

Thanks

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

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

Regards
Zubair

Please try my custom visuals

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.