Values over time

[Hascins]

Hi,

I have a table that basically looks like this:

http://imgur.com/a/jcaxE

Now I would like to create a measure that calculates the differences in value over time. See column Change

http://imgur.com/a/dey2a

The date of the table above is connected to a datetable just in case this could be a part of the solution that looks like this:

Date =
 ADDCOLUMNS (
 CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"Today";today();
"Yesterday";today()-1;
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
 "Year"; YEAR ( [Date] );
 "Monthnumber"; FORMAT ( [Date]; "MM" );
 "YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
 "YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
 "MonthNameShort"; FORMAT ( [Date]; "mmm" );
 "MonthNameLong"; FORMAT ( [Date]; "mmmm" );
 "DayOfWeekNumber"; WEEKDAY ( [Date] );
 "DayOfWeek"; FORMAT ( [Date]; "dddd" );
 "DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
 "Quarter"; "Q" & FORMAT ( [Date]; "Q" );
 "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" );
 "Heute -1 ";if([date]>=NOW()-2;"Y"; "N");
 "Heute -2";if([date]>=NOW()-3;"Y"; "N");
"Date-1";[Date]-1   )

I tried a lot of things but i cant find a proper solution for this. I would really appreciate it if someone could help!

[Hascins]

If this was asked before pls give me a pointer...i didn't find anything that actually helped

[v-ljerr-msft · Accepted solution]

Hi @Hascins,

Based on my test, you should be able to follow steps below to get your expected result. 

1. Add an Index column to your table in Query Editor under Add Column tab.

2. Then you should be able to use the formula below to create a new calculate column in your table.

Change =
Table1[Value]
    - CALCULATE (
        MAX ( Table1[Value] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Index]
                = EARLIER ( Table1[Index] ) - 1
                && Table1[Product] = EARLIER ( Table1[Product] )
        )
    )

Regards

[Hascins]

Thank you very much!