DAX Calculation

[ankush-vem]

Hi Team, 

I need your help in writing a dax formula to get the result from table on the right hand side. 

The Current table has a list of columns such as Date, No. of Shares, Close Price and Amount. The Amount column is calculated when we multiply No. of Shares and Close Price. 

To get the result from the Desired Result table, 

I have a Filter Option in the Power BI Report to filter out the Date. 

I would like to create a DAX Formula in Power BI which helps me to Select the Date from the Date Filter which would return the dates until the selected date and takes in consideration the Close Price of the selected date and applies the same close price for the previous dates and use that close price to multiply the no. of shares to get the results in Amount column. 

I.e., 

Formula = When clicked on the Selected Date = Apply Close Price of selected Date for the previous Dates * No. of Shares 

In this case, 
I have Selected the date to be 25/01/2021, where the close price is 16 and it applies to the previous dates as well to multiply the no. of shares to get the Amount as a result.

Thanks 

Ankush

[amitchandak · Accepted solution]

@ankush-vem , Try a measure like

measure =
var _max = maxx(allselected('Date') ,[Date])
var _price = calculate(Max(Table[price]), filter(allselected(Table), Table[Date] = _max))
return
sumx(Table, Table[No of shares] * _price )

[uthpalakinivita]

Hi Amitchandak,
Thank you for your reply and it retuns a value but no way of seeing its correct or wrong. Can we split this messure into two? one for the selected date's close price and then we can multiply it by the no of shares?

Also can you advise on how we can filter the ledger records on and before the selected date.
Thank you,
Uthpala