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% = V...
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
        )
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