Rate of Change Year-Quarter-Month with Segmentation

[geneanlisboa]

We've been trying to produce a report that displays curves representing the variation of a given measure over time where the start of the curve is the reference (starting in zero). We would like to split it (say, if there are 2 values for one category, we would like to compare them) and also have the ability to move from Months to Quarters to Years adjusting this calculation accordingly.

The best approximation to this requirement is a comparison of 2 symbols (corresponding to a category with 2 values) in yahoofinance:

The closest we got was to make a calculated value and a separate date table, but that is not working.

[geneanlisboa]

Our main difficulty is to record the left most reference value to generate the %. This left most reference value in the display has to change accordingly whenever the range (start-end date displayed) changes.

[v-yuta-msft]

Hi geneanlisboa,

Create three caluculate columns and use DAX formula like this pattern

Month Change Rate =
 (
    CALCULATE (
        SUM ( table[value] ),
        FILTER ( table, table[category] = EARLIER ( table[category] ) )
    )
        - CALCULATE (
            SUM ( table[value] ),
            table[category] = EARLIER ( table[category] ),
            table[date] = PREVIOUSMONTH ( table[date] )
        )
)
    / CALCULATE (
        SUM ( table[value] ),
        table[category] = EARLIER ( table[category] ),
        table[date] = PREVIOUSMONTH ( table[date] )
    )

Quarter Change Rate =
 (
    CALCULATE (
        SUM ( table[value] ),
        FILTER ( table, table[category] = EARLIER ( table[category] ) )
    )
        - CALCULATE (
            SUM ( table[value] ),
            table[category] = EARLIER ( table[category] ),
            table[date] = PREVIOUSQUARTER ( table[date] )
        )
)
    / CALCULATE (
        SUM ( table[value] ),
        table[category] = EARLIER ( table[category] ),
        table[date] = PREVIOUSQUARTER ( table[date] )
    )

Year Change Rate =
 (
    CALCULATE (
        SUM ( table[value] ),
        FILTER ( table, table[category] = EARLIER ( table[category] ) )
    )
        - CALCULATE (
            SUM ( table[value] ),
            table[category] = EARLIER ( table[category] ),
            table[date] = PREVIOUSYEAR ( table[date] )
        )
)
    / CALCULATE (
        SUM ( table[value] ),
        table[category] = EARLIER ( table[category] ),
        table[date] = PREVIOUSYEAR ( table[date] )
    )

Regards,

Jimmy Tao

[geneanlisboa]

Thanks a lot Jimmy. We've tried your solution but the PREVIOUSMONTH/QUARTER/YEAR would not be accepted by the context formula because it returns a table in a row context. Are we missing something? Perhaps we should split out and verify Month, Quarter and Year one by one?

[v-yuta-msft]

Hi geneanlisboa, 

Modify the formula DAX like this:

Month Change Rate =
 (
    CALCULATE (
        SUM ( table[value] ),
        FILTER ( table, table[category] = EARLIER ( table[category] ) )
    )
        - CALCULATE (
            SUM ( table[value] ),
            table[category] = EARLIER ( table[category] ),
            PREVIOUSMONTH ( table[date] )
        )
)
    / CALCULATE (
        SUM ( table[value] ),
        table[category] = EARLIER ( table[category] ),
        PREVIOUSMONTH ( table[date] )
    )

Regards,

Jimmy Tao