Buy sell dates in sales

Roshlin

Hi Vidas,

First of all congratulations for your excellent Portfolio Slicer.
I have just basic knowledge of Excel so would like to consult before fidgeting with the sheet.

I would like to have the purchase and sales date on the Sales report to be able to calculate the days of the trade and the make a scatter chart with the closed trades. symbol on one axis, days on the other and size of bubble could be the profit.

Is that posible???

I tried looking for the dates in Power BI but could only find the first trade date not the sale date.

Thanks for your help.
Prakash

VidasMatelis

Hi,

Take a look at Sales worksheet - you can see there fields that were used to get sales dates, amounts and Sales Capital Gain.
Generally, you can use Dates[Date] field with Report[Trans Total Amnt] and filters on TransType to get Buy/Sell transactions and then show data in different forms.

Roshlin

Thanks for your quick reply.
I'll give it a try.

Roshlin

Hi,
I gave it a try but not getting the resulsts.
I managed to put First ever buy date in values without problem
But when I put Dates[Date] field, the only options I get are: earliest, latest and count, none of them is the sales date.
If I put this field in rows, I get the dates but the Qty Bought column gets empty and annualized RoR gives odd figures.
If I put it in the columns it creates a columns for each date

I'm doing this in the Power BI Desktop Sales tab.

Where am I going wrong???

Thanks for your help.

VidasMatelis

Hi,

In PowerBI desktop start new sheet and drop Dates[Date] into report area. This will create TABLE with one column - Date. Next from Table Report select field "Sales Amnt" and drop into the same table. Now you will see sales report with sales amount for dates when sales were made.
Based on what you described, you are dropping Dates[Date] into Table and then trying to specify Aggregation function "Earliers, Latest or count", but that is not how PowerBI works.
So start with Table - get data that you want first into Table format. Then when you know how to get data, copy that table (CTRL+C, CTRL+V) and on the second table instance start to change to chart you want.

Most of the calculations will be under "Report" table. Most of the calculations are for selected date, for example:
- Annlzd RoR for 2018-02-22 is your Rate of Return for ONE day annualized. That would not make any sense. But if instead of the Day in each row you show Year, that makes perfect sense
- Dividends for 2018-02-22 is dividends received on that ONE day, again same value for Month/Year makes much more sense.

Some calculations shows value at the end of the day, for example: Total Value for 2018-02-22 is total portfolio value at the end of the day of 2018-02-22.

HTH,

Roshlin

Hi,
As per your instruccions, now I have the sales date and some of the other fields from your sales table.
But still cannot find the purcahse date. and then I would have to get a calulated field to get the days for the duration of the trade.

I'm using the Power BI for the first time and a bit lost. Thought it would be as easy as in excel where I just substarct sale date with purchase date to get days held.

Sorry for the trouble I'm causing. But I think knowing the duration of the trade helps, in fact it would be nice to have it in the open trades too.

Thanks for your help

VidasMatelis

If I understood correctly what you want, then you will need to add 2 new measures:

Latest Buy Date = CALCULATE(MAXA(Trans[Date])
	, DATESBETWEEN(Dates[Date], Blank(), MAX(Dates[Date]))
	, All(Dates)
    , FILTER('TransType', 'TransType'[TransTypeGroup] = "Buy" || TransType[TransType]="SymbolTransferIn")
   )

And then another measure:

Days Since Latest Buy = IF([Qty Held] <> 0, DATEDIFF([Latest Buy Date], LASTDATE('Dates'[Date]), DAY))

Roshlin

Hi,

Yes, that's what I wanted, and it works fine on the holdings page but comes blank on the sales page.
I think second measure will be different. If you could help out.

Thanks, have a nice day.

VidasMatelis

For Sales report you would need different 2 measures:

Latest Sell Date = CALCULATE(MAXA(Trans[Date])
	, DATESBETWEEN(Dates[Date], Blank(), LastDate(Dates[Date]))
    , FILTER('TransType', 'TransType'[TransTypeGroup] = "Sell" || TransType[TransType]="SymbolTransferOut" || TransType[TransType]="SymbolTransferOutAsSale")
   )

Sales Days Since Latest Buy = IF([Qty Sold] <> 0, DATEDIFF([Latest Buy Date], [Latest Sell Date], DAY))

I would also adjust:

Latest Buy Date = CALCULATE(MAXA(Trans[Date])
	, DATESBETWEEN(Dates[Date], Blank(), LASTDATE(Dates[Date]))
    , FILTER('TransType', 'TransType'[TransTypeGroup] = "Buy" || TransType[TransType]="SymbolTransferIn")
   )

Roshlin

Hi Vidas,

Just found something better than Portfolio Slicer.....
Thats your support, absolutely amazing.

Acheived what I wanted.
Now I can see on the holdings table how many days have passed sinced I entered the trade .
And on a scatter chart the profit and unrealized gain for the days passed.

Attaching screenshots of table, chart and my settings incase any other newbie wants to try. Maybe its not the correct way to do it but works for me.

Thanks again for your excelent Portfolio Slicer which is perfect out of the box and now gives me that little piece of extra info that I needed.