previous value option

[rawmeat]

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

[Zubair_Muhammad]

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

[rawmeat]

Thanks much it works perfect.

[rawmeat]

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

[Zubair_Muhammad · Accepted solution]

@rawmeat

Please see attached file

[rawmeat]

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