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

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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)