Percentage Change Over Time

[kfitzek]

Hello,

I am looking to calculate a % change over mutliple data sets. Essentially I have a file for each month with a certain amount of forecast. I want to be able to calcualte the difference between each month, but have it be flexible. So instead of having a multiple measures that compares January to Febuary and then January to March and so on, I would like to see if it's possible that I could select the comparisons I want it to do. For instance, compare March to June, or January to December. Something that is more live than stagnant. I pasted my current formula below to get a stagnant outcome, but want something more live if possible. 

Sum of TOTAL Forecast Mar % difference from Sum of TOTAL Forecast Jan =

VAR __BASELINE_VALUE = SUM('RM Global Summary'[TOTAL Forecast Jan])

VAR __VALUE_TO_COMPARE = SUM('RM Global Summary (2)'[TOTAL Forecast Mar])

RETURN

    IF(

        NOT ISBLANK(__VALUE_TO_COMPARE),

        DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)

    )

[DataNinja777 · Accepted solution]

Hi @kfitzek ,

To calculate a dynamic percentage change between any two months in your forecast data, you should first ensure your model uses a normalized structure with a proper Date column and a related calendar table. Assuming you’ve unpivoted your forecast data so that each row has a Date and Forecast value, and you've created a DateTable with continuous dates and related it to your forecast table on the Date column, you can then introduce two disconnected copies of the DateTable for comparison. These disconnected tables will allow users to select the base and comparison dates using slicers without interfering with the main model filters.

You can then write the following DAX measure:

% Change Between Selected Dates = 
VAR BaseDate = SELECTEDVALUE('DateTable_Base'[Date])
VAR CompareDate = SELECTEDVALUE('DateTable_Compare'[Date])
VAR BaseValue = 
    CALCULATE(
        SUM('ForecastTable'[Forecast]),
        'DateTable'[Date] = BaseDate
    )
VAR CompareValue = 
    CALCULATE(
        SUM('ForecastTable'[Forecast]),
        'DateTable'[Date] = CompareDate
    )
RETURN
    IF(
        NOT ISBLANK(CompareValue) && NOT ISBLANK(BaseValue),
        DIVIDE(CompareValue - BaseValue, BaseValue)
    )

This approach uses clean modeling, avoids hardcoding, and leverages a proper date relationship for accuracy and flexibility. Users can now choose any two months or dates using slicers tied to the disconnected base and comparison date tables, and the measure will compute the percentage change between the corresponding forecast values dynamically.

Best regards,