topic Re: how to calculate data with double and same date in DAX Commands and Tips

how to calculate data with double and same date

camelia_ — Tue, 09 Aug 2022 00:55:26 GMT

hiiii

Currently I have this dax function

Relative Price = IF(COUNT('Sample Transactions'[Buy/sell reference])= -1, SUM('Sample Transactions'[Total Trade Price*])-SUM('Price'[PRICE] ), IF(COUNT('Sample Transactions'[Buy/sell reference])=1, -1*(SUM('Sample Transactions'[Total Trade Price*])-SUM('Price'[PRICE])),""))

From this fuction, I am able to calculate the relative price, but only when the date is not the same (for example 1/1/22 = 22.31 , 2/1/22 = 22.56 ), but it can't calculate when the values are in the same date ( for example 3/1/22 = 22.45 , 3/1/22 = 23.00)

can someone help me? Thanks in advance!

Re: how to calculate data with double and same date

amitchandak — Tue, 09 Aug 2022 04:04:00 GMT

camelia_ ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: how to calculate data with double and same date

camelia_ — Tue, 09 Aug 2022 04:46:47 GMT

amitchandak ,I am sorry that i really can't share any of the data, but the problem is that for example

from Sample Transactions excel sheet, i have a list of traded date and price, somewhat like this

Traded date	Traded price	Buy/Sell	Buy/Sell reference
1/1/22	22.56	buy	1
2/1/22	22.5	buy	1
3/1/22	22.0	sell	-1
3/1/22	22.31	sell	-1
4/1/22	23.0	buy	1

and the market price is from Price excel sheet, from this sheet is just a list of price based on everyday date, no duplicate date

so when i put it in power bi, i use the above measure to calculate the relative price, but for same date like the above table, (3/1/22), it come out blank in matrix table visualisation.

for creating the matrix table visualisation, i create relationship as below,

isin with isin (reference > price & sample transactions) , date with date (timeline > price & sample transactions)

do you happen to know how to fix this ?

and i have another problem, for relative price & gain/loss, i create function in power bi (like above), but these measure can't be summarized( don't have the summarize category in measure tool>properties),so i can't put these in the card visualisation.

Gain / Loss = IF(COUNT('Sample Transactions'[Buy/sell reference])= -1, SUM('Sample Transactions'[Total Trade Price*])-SUM('Price'[PRICE] ) / SUM('Price'[PRICE]), IF(COUNT('Sample Transactions'[Buy/sell reference])=1, -1*(SUM('Sample Transactions'[Total Trade Price*])-SUM('Price'[PRICE])) / SUM('Price'[PRICE]),""))

How to fix this?