comparing the latest two values

[aselad]

I have the closingprices dataset(as shown in the image to the left) which has the closingprice by securityID and the trading date. The actualy dataset contains more than 200k rows. 

In the report, a user selects a date and need to scan the closingprices table and generate the summarized view as shown in the right.

for example, on the 14th April 2021, two securityIDs have increase their prices(highlighted in green) compared to the previous traded date and one securityID had decreased prices(highlighted in red). 

I have tried to do this using earlier functions but is giving me memory issues as the row count is exceeding 200k. 

As the analysis need to check only two days per securityID, I am thinking is there a way to create a virtual table which I can then use to get to the summarized view. 

Highly appreciate help to solve this. 

[v-cazheng-msft · Accepted solution]

Hi @aselad 

In Power BI, you can create Calculated tables to get a new table. However, these tables will be stored in the model once they are created and their values are stable. They can't change according to the selected value of the users. These characteristics also apply to Calculated columns.

If you want to create a summarize table as the image, you must need a column with Positive/Negative as its values. Then it's hard to avoid using EARLIER. Therefore, you can considering Filter Rows in Power Query Editor to remove unnecessary rows before loading data into Power BI and then create Calculated columns and Measures as the attached sample file.

If you omit the operation that let the user choose the date, then the latest date will be determined. In this case, you can create a Calculated table and do calculations with it.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg]

can you provide data in text form?

[aselad]

SecurityID Trade Date Closing Price
aaa 14-Apr-21 50
aaa 12-Apr-21 10
aaa 8-Apr-21 5
bbb 12-Apr-21 45
bbb 10-Apr-21 48
bbb 9-Apr-21 40
ccc 14-Apr-21 30
ccc 13-Apr-21 25
ddd 14-Apr-21 34
ddd 10-Apr-21 40
ddd 8-Apr-21 34
ddd 6-Apr-21 25
ddd 5-Apr-21 20

[vanessafvg]

did you want a new table or you can also create a measure like this which will return a status for each line

see attached a power bi

ChangeStatus =

var securid = CALCULATE(SELECTEDVALUE(ClosingPrice[SecurityID]))

var maxdate = CALCULATE(max(ClosingPrice[Date]), ClosingPrice[SecurityID] = securid)

var maxprice = CALCULATE(sum(ClosingPrice[ClosingPrice]), ClosingPrice[SecurityID] = securid && ClosingPrice[Date] = maxdate)

var prevdate = CALCULATE(MAX(ClosingPrice[Date]), ClosingPrice[Date] < maxdate)

var prevprice = CALCULATE(sum(ClosingPrice[ClosingPrice]), ClosingPrice[SecurityID] = securid && ClosingPrice[Date] = prevdate)

return

if (maxprice < prevprice, "Negative", "Positive")