Forum Discussion
previous value option
Hi all,
I am trying to migrate one of the report from tableau to power bi.
We have a table like below:
Sample table:
Name Date Value
A 4/19 10
A 4/21 20
B 4/20 10
And we want to create a table in power bi which can inherit the previous date value.
Like this one:
Name Date Value
A 4/19 10
A 4/20 10
A 4/21 20
B 4/19 null
B 4/20 10
B 4/21 10
The challenge here is we don't have A for Date 4/20 or B for Date 4/19 or 4/21 in the database.
In tableau we have previous_value expression to loop through all the date without a context filter so I want to ask if there is an option in power bi which can give me the result above without adding date and value in the original database.
THX
5 Replies
Hi rawmeat
try this calculated table
From Modelling Tab>>NEW TABLE
New Table = VAR AllCombinations = CROSSJOIN ( VALUES ( Table1[Date] ), VALUES ( Table1[Name] ) ) RETURN ADDCOLUMNS ( AllCombinations, "Value", VAR mydate = CALCULATE ( MAX ( Table1[Date] ), FILTER ( table1, Table1[Name] = EARLIER ( [Name] ) && Table1[Date] <= EARLIER ( [Date] ) ) ) RETURN CALCULATE ( SUM ( Table1[Value] ), FILTER ( Table1, Table1[Name] = EARLIER ( [Name] ) && Table1[Date] = mydate ) ) )Sorry to bother again, just one more question what if I have multiple value for one day and want to aggregate.
For example
Name Date Value
A 4/19 10
A 4/19 10
A 4/21 30
B 4/20 10
To be
Name Date Value
A 4/19 20
A 4/20 20
A 4/21 30
B 4/19
B 4/20 10
B 4/21 10
Really appreciate your help
Thanks much it works perfect.
Ok, I got it, I use summerize to create a new table.
Test = SUMMARIZE(Table1,Table1[Name],Table1[Date],"Value",SUM(Table1[Value]))
Thanks again Muhammed
