Forum Discussion

Johnsnowlife's avatar
Johnsnowlife
Icon for Helper III rankHelper III
9 years ago
Solved

Show Change between Min and max Dates

I am trying to show the change in the [% of Fund] for each instrument in the portfolio from the earliest date in the filter period to the latest date in the filter period. So far I have

Change% = 
VAR MaxDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR MinDate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    CALCULATE (
        SUMX ( DataAllFunds, DataAllFunds[% of Fund] ),
        DataAllFunds[Date] = MaxDate
    ) - CALCULATE(
		SumX ( DataAllFunds ,DataAllFunds[% of Fund]), DataAllFunds[Date] = MinDate)

Plotting Instrument against %Change gives me the correct values.

 

But I want to add the change Column into my table which currently shows the holding for the Latest Date in the table and only the instruments classified as "Equity". Then my %Change becomes zero for everything.  

Where am I going wrong? 

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    Johnsnowlife

    It is the filter that affects the expected output. Try to add ALLEXCEPT to your measure. If it doesn't work, please upload a sample pbix file.

     

    Change% =
VAR MaxDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR MinDate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    CALCULATE (
        SUMX (
            ALLEXCEPT ( DataAllFunds, DataAllFunds[InstrCode] ),
            DataAllFunds[% of Fund]
        ),
        DataAllFunds[Date] = MaxDate
    )
        - CALCULATE (
            SUMX (
                ALLEXCEPT ( DataAllFunds, DataAllFunds[InstrCode] ),
                DataAllFunds[% of Fund]
            ),
            DataAllFunds[Date] = MinDate
        )

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Johnsnowlife

    It is the filter that affects the expected output. Try to add ALLEXCEPT to your measure. If it doesn't work, please upload a sample pbix file.

     

    Change% =
VAR MaxDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR MinDate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    CALCULATE (
        SUMX (
            ALLEXCEPT ( DataAllFunds, DataAllFunds[InstrCode] ),
            DataAllFunds[% of Fund]
        ),
        DataAllFunds[Date] = MaxDate
    )
        - CALCULATE (
            SUMX (
                ALLEXCEPT ( DataAllFunds, DataAllFunds[InstrCode] ),
                DataAllFunds[% of Fund]
            ),
            DataAllFunds[Date] = MinDate
        )
    • Johnsnowlife's avatar
      Johnsnowlife
      Icon for Helper III rankHelper III
      8 years ago

      Thanks Eric! This is what I ended with.

       

      Change%M = 
VAR MaxDate =
    CALCULATE ( MAX ( 'DataAllFunds'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR MinDate =
    CALCULATE ( MIN ( 'DataAllFunds'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    CALCULATE (
        SUMX ( DataAllFunds, [Fund % M] ),
        DataAllFunds[Date] = MaxDate
    ) - CALCULATE(
		SumX ( DataAllFunds ,[Fund % M]), DataAllFunds[Date] = MinDate)

       

       

      Where Fund % M is 

      Fund % M = 
VAR Fund =
    DISTINCT ( DataAllFunds[Fund] )
VAR TDate =
    MAX ( DataAllFunds[Date] )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( DataAllFunds[Market Value] ),
            DataAllFunds[Fund] = Fund,
            DataAllFunds[Date] = TDate
        ),
        CALCULATE (
            SUM ( DataAllFunds[Market Value] ),
            FILTER (
                ALL ( DataAllFunds ),
                DataAllFunds[Fund] = Fund
                    && DataAllFunds[Date] = TDate
            )
        ),
        0
    )
        * 100