Forum Discussion
Solved
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 ...
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
