Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Please see attached file
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 ) ) )
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
Please see attached file
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