Month-Over-Month on Multiple Items

[FinanceBI]

Hello All,

I'm looking to calculate the month-over-month return for multiple investments funds. From my understanding, I cannot use the quick measure because I would need to select one base value, but I'm looking to calculate the month-over-month change for a number of funds. 

Is it possible to create calculated columns based on the data in a visual? If not, is it possible to get my query to look like the matrix down below, that way I can create some calculated columns to accomplish my goal?

I'm open to any ideas. 

Currently, my query is structured like this:

I created a matrix that looks like this:

[FinanceBI]

Hi all, 

I'm looking for the easiest way to calculate month over month change for data structured in the following way. I've tried to use the Time Intelligence Month-over-Month Change quick measure, where Base = Return and Date = Return Date. My date is column is set up as a hierarchy, however, this still doesn't work. 

I see some tutorials, but they all seem overly involved for such a simple calculation. Perhaps it would be easier to just use a Python script to edit my query?

Fund      Return    Return Date

  A             8%       1/31/2019

  A             7%       2/28/2019

  A             8%       3/31/2019

  B             6%       1/31/2019

  B             6%       2/28/2019

  B             5%       3/31/2019

  C            10%      1/31/2019

  C            11%      2/28/2019

  C            12%      3/31/2019

[Anonymous · Accepted solution]

I attached the pbix file below, but here's what I did:

• Need a dedicated Calendar/Date table. Small function in Power Query that will create a basic one for you
• Once that is loaded, be sure to mark the DimDate table as a Date Table.  Need to do this to ensure the time intelligence function below will work correctly. 
• Relate that to your Fact table.  Will use the DateKey from the DimDate table as your filters (sidebar: you may want to have other dimesion tables for the Name and Strategey since you really dont want to filter fact tables, but if the table is not too big it should work fine)

Create these measures:

Just a simple average. 

Simple Average = AVERAGE( FactReturn[Monthly Return Rate] )

MoM Change = 
//Checks to see if there is a monthly return
IF ( 
    NOT( 
        ISBLANK([Simple Average])
    ),

//Checks to see of the return is the first in the return stream
//if so, it will return First Date, but can put anything there
    IF(
       CALCULATE( 
           COUNTROWS( FactReturn ), 
            FILTER( 
                ALL( DimDate), 
                DimDate[DateKey] <= max( DimDate[DateKey]))
       ) =1,
        "FirstDate"
    ,

//Assuming their is a return and its not the 1st in the stream
//Takes the average return in current filter context and subtracts
//The return from the previous month 
    [Simple Average] - 
        CALCULATE( 
            [Simple Average], 
            PREVIOUSMONTH ( DimDate[DateKey])
        )
    )
)

Final Matrix:

Still the issue what you would wnt to do at the total level. Could be average of the changes, whatever the last change was, or maybe dont even need them. But should be a good start

File:

https://1drv.ms/u/s!Amqd8ArUSwDS0zAk-xUYbEuuGXI8

[FinanceBI]

Thanks for the help! I got this to work. The attached file was very helpful! 

I added a "/[Simple Average]" to the end of the MOM field in the DimDate table to get the month over month percentage change. 

Thanks a bunch for the help!

[FinanceBI]

Thanks for the help Nick!! Using your template, I was able to accomplish exactly what I set out to. 

This dashboard will be used to validate investment returns that we have to enter by hand. Here is the part that Nick helped me with! 

[Anonymous]

awesome! Glad I was able to get you in right direction. Good stuff