Creating a table of custom measures and their difference

[Johnsnowlife]

I am trying to recreate a table of performance values for a mutual fund for different periods. 

Data table is formatted as below. 

I've created measures for each period with the below an example of 1yr performance. 

Perf1yrRoll = 
VAR EndDate = 
	CALCULATE(MinX(AllPerfMnthly,AllPerfMnthly[Date]),ALLSELECTED ( 'Calendar'[Date] ) )
VAR BeginDate =
    CALCULATE ( EOMONTH(EDATE(EndDate,-12),0) , ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
        CALCULATE (
            PRODUCTX ( 'AllPerfMnthly', ( 1 + 'AllPerfMnthly'[PerfMnthly] ) )
                ,
            FILTER (
                ALLEXCEPT ( 'AllPerfMnthly', AllPerfMnthly[Portfolio] ),
                'AllPerfMnthly'[Date] > BeginDate
                    && 'AllPerfMnthly'[Date] <= EndDate )
            )
		- SUMX(AllPerfMnthly,1)

I now have this table which shows each measure in a separate column.

.  

Instead of showing a total, I want to show a difference of Top - Bottom. How can I do this? 

Would it be better to use a matrix somehow?

[Ashish_Mathur]

Hi,

So instead of showing -97.7% for 3M, you want to show 1.82%-0.4%=1.4%.  The same should happen for other time periods as well.  Am i correct?

[Johnsnowlife]

Hi, it needs to be the other way around. The Top minus the bottom. So 0.4% - 1.82% = -1.42%. And it should happen for all periods. 

[Ashish_Mathur]

Hi,

This sounds doable.  Share the download link of your PBIX file.

[Johnsnowlife]

Here you go. 

http://www.filehosting.org/file/details/699711/Performance%20Measures%20Example.pbix

[Ashish_Mathur]

Hi,

That site asks me for my e-mail address.  Sorry, cannot share that.

[Johnsnowlife]

Sorry, try this. 

Performance File

[Ashish_Mathur]

Hi,

That link did not work.  Here's the message i got

We're sorry, but my_email can't be found in the aylett-my.sharepoint.com directory. Please try again later, while we try to automatically fix this for you.

[Johnsnowlife]

Sorry try again. 

Performance File

Sorry, I'm new to this...

[Ashish_Mathur]

Hi,

I am not sure of what your formula does, bu here is how i modified your formula for 3m

== 
if(HASONEVALUE(AllPerfMnthly[Portfolio]),VAR EndDate = 
	CALCULATE(MinX(AllPerfMnthly,AllPerfMnthly[Date]),ALLSELECTED ( 'Calendar'[Date] ) )
VAR BeginDate =
    CALCULATE ( EOMONTH(EDATE(EndDate,-3),0) , ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
        CALCULATE (
            PRODUCTX ( 'AllPerfMnthly', ( 1 + 'AllPerfMnthly'[PerfMnthly] ) )
                ,
            FILTER (
                ALLEXCEPT ( 'AllPerfMnthly', AllPerfMnthly[Portfolio] ),
                'AllPerfMnthly'[Date] > BeginDate
                    && 'AllPerfMnthly'[Date] <= EndDate )
            )
		- SUMX(AllPerfMnthly,1),MAXX(ALLSELECTED(AllPerfMnthly[Portfolio]),VAR EndDate = 
	CALCULATE(MinX(AllPerfMnthly,AllPerfMnthly[Date]),ALLSELECTED ( 'Calendar'[Date] ) )
VAR BeginDate =
    CALCULATE ( EOMONTH(EDATE(EndDate,-3),0) , ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
        CALCULATE (
            PRODUCTX ( 'AllPerfMnthly', ( 1 + 'AllPerfMnthly'[PerfMnthly] ) )
                ,
            FILTER (
                ALLEXCEPT ( 'AllPerfMnthly', AllPerfMnthly[Portfolio] ),
                'AllPerfMnthly'[Date] > BeginDate
                    && 'AllPerfMnthly'[Date] <= EndDate )
            )-1
		)-(MINX(ALLSELECTED(AllPerfMnthly[Portfolio]),VAR EndDate = 
	CALCULATE(MinX(AllPerfMnthly,AllPerfMnthly[Date]),ALLSELECTED ( 'Calendar'[Date] ) )
VAR BeginDate =
    CALCULATE ( EOMONTH(EDATE(EndDate,-3),0) , ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
        CALCULATE (
            PRODUCTX ( 'AllPerfMnthly', ( 1 + 'AllPerfMnthly'[PerfMnthly] ) )
                ,
            FILTER (
                ALLEXCEPT ( 'AllPerfMnthly', AllPerfMnthly[Portfolio] ),
                'AllPerfMnthly'[Date] > BeginDate
                    && 'AllPerfMnthly'[Date] <= EndDate )
            )-1
		))
)

Please not that i changed the SUMX(AllPerfMnthly,1) portion of your fomrula to -1 in my MINX and MAXX.  Here's my file.

[v-caliao-msft]

@Johnsnowlife,

Instead of showing a total, I want to show a difference of Top - Bottom. How can I do this? 

Currently, we have the options that may be available for aggregating a field:

• Do Not Summarize
• Sum
• Average
• Minimum
• Maximum
• Count
• Standard deviation
• Variance
• Median

The total row will show corresponding value based on our selection. As you can see, there is no difference aggregate function currently.  In your scenario, there are only two rows, if we have a difference aggregate fucntion with multiple rows, which rows difference should it calculated? So I am afraid there is such an option to allow us to show difference. What we can do is that calculated difference between two columns.

Regards,

Charlie Liao

[Johnsnowlife]

Alright, I see that would be problematic.

Is there a way I could get it to work with the Portfolios as columns and the different periods as rows. With a third column for the difference?