cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,798)