Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 ) | ||||
Date | Product | Total Left | Amount Left Day Before | Last Movement Day | Last Movement Day |
03/03/2018 | Water | 34 | 0 | 03/03/2018 | 03/03/2018 |
03/04/2018 | Water | 34 | 34 | 03/03/2018 | (blank) |
03/05/2018 | Water | 34 | 34 | 03/03/2018 | (blank) |
03/06/2018 | Water | 140 | 34 | 03/06/2018 | 03/06/2018 |
03/07/2018 | Water | 160 | 140 | 03/07/2018 | 03/07/2018 |
03/08/2018 | Water | 160 | 160 | 03/07/2018 | (blank) |
03/09/2018 | Water | 160 | 160 | 03/07/2018 | (blank) |
03/10/2018 | Water | 230 | 160 | 03/10/2018 | 03/10/2018 |
Solved! Go to Solution.
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] )
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
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
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] )
Thanks a lot !! This worked !!
Thanks for helping, but a dax solution is a better way for me!!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
107 | |
105 | |
89 | |
61 |
User | Count |
---|---|
168 | |
138 | |
134 | |
102 | |
86 |