Forum Discussion

rawmeat's avatar
rawmeat
Frequent Visitor
8 years ago
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 ...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
rawmeat
Frequent Visitor
8 years ago

Thanks much it works perfect.

  • rawmeat's avatar
    rawmeat
    Frequent Visitor
    8 years ago

    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