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

DAX DAX Calculations
1

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.

2

@ambepat,

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
3

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. :slightly_smiling_face:

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

4

@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.

5

Hi @Harsh,

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

6

Hello @ambepat,

You’re Welcome. :slightly_smiling_face:

We’re glad that we were able to help you.

Thanks and Warm Regards,
Harsh

7

@ambepat,

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

Glad we were able to save your sanity…

  • Brian
8

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!

9

Resolved

1 Like
10

Thank you, @ambepat. I’ve got an opportunity to learn from your question.