How to graph percent change over time

[Anonymous]

I have a Sales table as seen below(actually about 2000 rows from 4 different months).

ID	Sum	Date
1	10	2022-01
2	50	2022-02
3	100	2022-03
4	25	2022-04

I also have a calendar date table that shows all the dates between the first and last date, structured as follows.

Calendar = CALENDAR(FIRSTDATE(Sales[Date]),LASTDATE(Sales[Date])))

I want to graph the percent change over time between the months. Essentially it should graph the percent change from sum of ID 1 to 2 being 400%, 2 to 3 being 100%, and 3 to 4 being -75%. I am hoping to have this be dynamic so as I had new data, it will calculate the sum of percent change between months.

I've tried following quite a few online guides but I cannot get the formulas to graph this right.

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can try to use the following measure formula to use the id as index to loop and calculate the dynamic difference:

formula =
VAR currID =
    MAX ( Table[ID] )
VAR prevID =
    CALCULATE ( MAX ( Table[ID] ), FILTER ( ALLSELECTED ( Table ), [ID] < currID ) )
VAR currValue =
    SUM ( Table[Sum] )
VAR prevValue =
    CALCULATE (
        SUM ( Table[Sum] ),
        FILTER ( ALLSELECTED ( Table ), [ID] = prevID )
    )
RETURN
    IF ( prevValue <> BLANK (), DIVIDE ( currValue - prevValue, prevValue ) )

Regards,

Xiaxoin Sheng