cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rawmeat Regular Visitor
Regular Visitor

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: previous value option

@rawmeat

 

Please see attached file

 

21apr.png

Try my new Power BI game Cross the River

View solution in original post

5 REPLIES 5
Super User
Super User

Re: previous value option

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 )
            )
    )
Try my new Power BI game Cross the River
Super User
Super User

Re: previous value option

@rawmeat

 

Please see attached file

 

21apr.png

Try my new Power BI game Cross the River

View solution in original post

Highlighted
rawmeat Regular Visitor
Regular Visitor

Re: previous value option

Thanks much it works perfect.

rawmeat Regular Visitor
Regular Visitor

Re: previous value option

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

 

rawmeat Regular Visitor
Regular Visitor

Re: previous value option

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

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)