I have attempted a measure to calculate the value of the porfolio at a max date.

However, to my great surprise I thought it was fairly easy but the formula I wrote is not giving me the answer I need. I suppose I am misunderstanding a basic concept but I can’t seem to make it work.

Here is the Dax calc

PortfolioSumatMaxDate =
VAR MaxDate =
CALCULATE ( MAX ( Crypto[Date] ), ALL ( Crypto ) )
VAR SumofPortfolioValue =
SUM ( Crypto[Portolio Value] )
VAR SumofPorfoioAtMaxDate =
CALCULATE (
SumofPortfoliovalue,
Dates[Date]=MaxDate)
RETURN
SumofPorfoioAtMaxDate

I am expecting a much smaller number cuz I thought I was isolating the date to one specific date but for some reason this dax calc is giving me the full value of porfolio not the porfolio at the max date. I am so confused why it would do that. Any help will be appreciated.

I believe the problem stems from how DAX treats variables as constants. When you have a CALCULATE statement where the first expression is a variable not within an iterator, the change in context ( in this case Dates[Date] = MaxDate ) will have no effect, since SumofPortfoliovalue is a constant.

To fix this, try creating SumofPortfoliovalue as a separate measure and calling that as your first expression within the CALCULATE statement.

From what I can see is you’ve referenced naked variable directly into the CALCULATE function.

PortfolioSumatMaxDate =
VAR MaxDate =
CALCULATE ( MAX ( Crypto[Date] ), ALL ( Crypto ) )
VAR SumofPortfolioValue =
SUM ( Crypto[Portolio Value] )
VAR SumofPorfoioAtMaxDate =
CALCULATE (
SumofPortfoliovalue,
Dates[Date]=MaxDate)
RETURN
SumofPorfoioAtMaxDate

Rather try this out -

PortfolioSumatMaxDate =
VAR MaxDate =
CALCULATE ( MAX ( Crypto[Date] ), ALL ( Crypto ) )
VAR SumofPorfoioAtMaxDate =
CALCULATE (
SUM ( Crypto[Portolio Value] ) ,
Dates[Date] = MaxDate)
RETURN
SumofPorfoioAtMaxDate

I’m providing a link below from the RADACAD channel where they’ve explained why one cannot use the naked variables directly inside the CALCULATE function.

Hoping this helps you and meets your requirements that you’ve been looking for.

Note:If this doesn’t solve your problem then please upload the PBIX file so that members of the forum can assist you in a better and efficient manner.

@BrianJ thanks for the explanation. I was going crazy. I thought there was a nuance I was missing. You are right basically the variable I created was constant or stored so by using it in a calculate the variable is a constant meaning it gives me the same answer. I knew there was something missing just could not get it. But thanks. Appreciate it.

Hi @ambepat, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!