Forum Discussion

Hascins's avatar
Hascins
Frequent Visitor
9 years ago
Solved

Values over time

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!

 

 

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Hascins,

     

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

     

    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

3 Replies

  • Hascins's avatar
    Hascins
    Frequent Visitor
    9 years ago

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

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      Hi Hascins,

       

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

       

      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