Help, friends. Is there a better way to implement this data model?

[sanshushu]

Help, friends. Is there a better way to implement this data model?

My English level is not good, now use translation tools, if you have not understand the place, welcome to leave a message oh.

I built a data model to measure the return rate of the stock market. The data sources include [Date], [Opening], [Lowest], [Maximum], [Close],

The following data is a function I wrote：

[Entrustment buy price] and [Entrustment sell price] for the next trading day

Entrustment buy price = [Close ]*(1-parameter)

Entrustment sell price = [Close ]*(1+parameter)

Successful device purchase when [Entrustment Buy price] > [Lowest] of next trading day;

Successful consignment sale occurs when [Entrustment sell price] is < [Maximum] on the next trading day

;

I wrote the judgment company as follows:

[Entrustment buy result] =

var _minnext = MINX(FILTER('table',[Date]>EARLIER('table'[Date]) ),[Date])

var _weimai = AVERAGEX(FILTER('table',[Date]=_minnext ),[Lowest])

return IF(_weimai<[Entrustment buy price],"Successful entrusted purchase","")

[Entrustment sell result] =

var _minnext = MINX(FILTER('table',[Date]>EARLIER('table'[Date]) ),[Date])

var _weimai = AVERAGEX(FILTER('table',[Date]=_minnext ),[Maximum])

return IF(_weimai>[Entrustment sell price],"Successful consignment sale","")

When Successful discrepancy purchase, only Successful consignment sale can continue to buy stocks;

[Position to judge] indicates whether it is available for purchase.

Position to judge =

var _wmjsjmax = MAXX(FILTER('table', [Date]

var _wmcsjmax = MAXX(FILTER('table',[Date]sale" ),[Date])

return

If (_wmcsjmax < _wmjsjmax, 1, 0)

[Position the results] represents the time node of (Successful discrepancy purchase) or (Successful consignment sale),

Position the results =

var _datemin = minx(FILTER('table', [Date]>EARLIER('FILTER'[Date])),[Date])

var _val = SUMX(FILTER('table', [Date]=_datemin),Position to judge])

return [Position to judge]-_val

Cost of purchase = ROUND( if([Position the results]=-1,[Entrustment buy price],BLANK()),2)

Sell price = ROUND( if([Position the results]=1,[Entrustment sell price],BLANK()),2)

Trading profit =

var _maxdate = MAXX(FILTER('table', [Date]BLANK()),[Date])

var _val = SUMX(FILTER('table', [Date] = _maxdate),[Cost of purchase])

return IF([Sell price]=BLANK(),BLANK(),DIVIDE([Sell price]-_val,_val))

_________________________________________________________________________________________

The calculated value of [Entrustment buy price] and [Entrustment sell price] is calculated through (parameters), but not through (create measures). Therefore, (parameter) can only be manually adjusted.

I hope to know different (parameters). When the value of (parameters) is what, Trading profit can be maximized.

And what better way to implement this data model?

Thank you for your help

Date	Opening	Lowest	Maximum	Close	Entrustment buy price	Entrustment sell price	Entrustment buy result	Entrustment sell result	Position to judge	Position the results	Cost of purchase	Sell price	Trading profit
2021/4/2	127.65	127.52	130.72	130.28	126.39	134.17	Successful entrusted purchase		0	-1	126.39
2021/4/6	130.36	125.92	130.59	127.92	123.84	132	Successful entrusted purchase		1	0
2021/4/7	128.3	123.82	130.21	130.15	126.24	134.06	Successful entrusted purchase	Successful consignment sale	1	1		134.06	6.07%
2021/4/8	126.15	126.13	134.45	134.13	129.85	138.41			0	0
2021/4/9	133.05	130.14	133.33	131.35	127.33	135.37	Successful entrusted purchase		0	-1	127.33
2021/4/12	131.26	127.29	131.36	129.09	125.03	133.15			1	0
2021/4/13	129.96	129.22	131.38	130.44	126.72	134.16			1	0
2021/4/14	129.28	127.07	131.28	129.69	126.34	133.04			1	0
2021/4/15	129.03	127.41	130.48	129.4	126.86	131.94	Successful entrusted purchase		1	0
2021/4/16	130.75	125.59	130.75	127.67	124.85	130.49		Successful consignment sale	1	1		130.49	2.48%
2021/4/19	128.35	126.36	130.55	129.39	126.5	132.28			0	0
2021/4/20	129.21	128.19	130.73	128.41	125.48	131.34			0	0
2021/4/21	127.67	127.02	129.18	128.32	125.7	130.94	Successful entrusted purchase		0	-1	125.7
2021/4/22	128.57	124.59	129.13	125.05	122.27	127.83		Successful consignment sale	1	1		127.83	1.69%
2021/4/23	125.05	124.6	131	130.59	127.46	133.72	Successful entrusted purchase		0	-1	127.46
2021/4/26	131.65	123.81	132.16	123.83	120.27	127.39			1	0
2021/4/27	124.05	123.82	125.32	124.85	121.4	128.3			1	0
2021/4/28	124.72	121.52	124.74	123.44	119.86	127.02		Successful consignment sale	1	1		127.02	-0.35%
2021/4/29	123.82	123.44	128.59	126.9	123.1	130.7		Successful consignment sale	0	0
2021/4/30	129.98	127.28	131.5	129.59	126.06	133.12	Successful entrusted purchase		0	-1	126.06
2021/5/6	129.59	125.04	131.05	126.12	123.03	129.21	Successful entrusted purchase		1	0
2021/5/7	125.98	121.02	127.04	121.58	117.93	125.23			1	0
2021/5/10	121.62	120.28	122.9	121.98	118.4	125.56		Successful consignment sale	1	1		125.56	-0.40%

[v-yiruan-msft]

Hi @sanshushu ,

I'm sorry I don't quite understand what you are looking for. Are you trying to get a dynamic parameter to get a maximum trading profit? If so, please refer to the following blog on using what if parameter to achieve it.

Make your data dynamic with Power BI “What if Analysis” Pt1

Power BI What If Parameter for Getting the Sales of X months ago: Use Case Scenario

If the above one can't help you, please provide your expected result with more details(backend logic, special examples etc.). Thank you.

Best Regards

[sanshushu]

Thanks for your reply, my friend.

My model looks something like this

One problem is that "what if parameter" is only "create measures", not "calculated columns".

Link: https://pan.baidu.com/s/15778Vy52FHaWXOVD4FsfHw 

Extraction code: 1234

[sanshushu]

help

I need you my friend

help

[sanshushu]

Because of the translation tool, this is a few errors,

See the screenshot for the correct code. Thank you very much!