Cross reference multiplication based on date and stock price

[Anonymous]

Dear all,

I have the following table:

What measure shall I use to achieve the highligted red figure. I need a measure/formula which would allow me to filter by client type, stock name as well as the date.

Warmest regards,

[v-shex-msft]

Hi TeeTreeThree,

I agree with CahabaData’s point of view, you could refer to below sample:

Dax: Stock Total = ADDCOLUMNS(ALL('Stock Record'),"Price",LOOKUPVALUE('Stock Detail'[Stock Price],'Stock Detail'[Date],'Stock Record'[Date],'Stock Detail'[Stock Name],'Stock Record'[Stock Name]))

Calculate coulmns of value and current quality:

Current Quality = SUMX(FILTER(all('Stock Total'),AND('Stock Total'[Date]<=EARLIER('Stock Total'[Date]) , 'Stock Total'[Stock Name]=EARLIER('Stock Total'[Stock Name]))),'Stock Total'[Quality])

value = ABS( 'Stock Total'[Quality] * 'Stock Total'[Price])

About total value, you could take a look at below formulas:

1. Measure.

TotalByDate = CALCULATE(SUM('Stock Total'[value]), Filter (ALL( 'Stock Total'), SUMX(FILTER('Stock Total', 'Stock Total'[Date] = Earlier ('Stock Total'[Date] ) ),'Stock Total'[value])))

2. Calculate column.(it seems simple than measure)

TotalValueByDate = SUMX( FILTER(ALL('Stock Total'),'Stock Total'[Date]=EARLIER('Stock Total'[Date])),'Stock Total'[value])

>>I need a measure/formula which would allow me to filter by client type, stock name as well as the date.

You could add the new condition to formula:

TotalByDate = CALCULATE(SUM('Stock Total'[value]), Filter (ALL( 'Stock Total'), SUMX(FILTER('Stock Total', AND('Stock Total'[Date] = Earlier ('Stock Total'[Date] , 'Stock Total'[TrxType]=”Sell”) ) ),'Stock Total'[value])))

In addition, I think use a slicer is more suitable to filter data.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi xiaoxin, thanks for replying. 

I realised the results you attain and the results I aim to get are different as seen in the above.

[v-shex-msft]

Hi TeeTreeThree,

>>I realised the results you attain and the results I aim to get are different as seen in the above.

Could you provide some detail about these?

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin,

I managed to find the solution to this by reading some of the post:

The relationship I built as follow:

The measure used to attain my desired results:

Total Value = SUMX(VALUES(Symbol[Symbol]),CALCULATE(SUM(Trx[Quantity]),DATESBETWEEN(DimDate[DateKey],BLANK(),MAX(DimDate[DateKey])),ALL(DimDate))*CALCULATE(VALUES(Price[Px]),LASTNONBLANK(DATESBETWEEN(DimDate[DateKey],DATEADD(DimDate[DateKey],-30,DAY),MAX(DimDate[DateKey])),CALCULATE(COUNT(Price[Px])))))

However:

I managed to attain what I want in this simplified example however when apply to actual data this error show

"A table of multiple values was supplied where a single value was expected."

[CahabaData]

I would trim out the unneeded columns in the upper sample, and then do a pivot.

Having said that; it seems viable if there is really a limited number of Stocks as per your sample.  If the reality is that there is an infinite variety of stocks then I wouldn't take this approach of a left-to-right style display and instead stay with the normal up/down table of data and go with grouping around the dates to sum.

[Anonymous]

Hi CahabaData,

Thank you for replying. As I am trying to construct a dashboard I would love to do it in PowerBI. I just need to get to results as shown in the red highlighted column - it can be a new column or new measure. Shall I join some tables together? I am open to whatever method to get the results except that it must be done in PowerBI.

Thank you,