# Am I missing something? Porfolio Value at Max date Dax calc

Hi Folks,

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.

• Brian

Hello @ambepat,

Thank You for posting your query onto the Forum.

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.

Thanks and Warm Regards,
Harsh

@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 @Harsh,

thanks for the article. Understand the concept and error now. Appreciate it.

Hello @ambepat,

You’re Welcome.

Thanks and Warm Regards,
Harsh

That was a quick one for me to recognize, since I’d made that same mistake many, many times before myself.