Forum Discussion

dkushner's avatar
dkushner
Frequent Visitor
8 years ago
Solved

calculating difference with the previous value

Hello everybody,

I need to find the difference between two rows within a table of values.

I have a table like

IDCounterNumberDateTSummAPlus
459866112018-01-25 13:00:00.00010289870
45986751392018-01-25 13:00:00.00019453840
45986761502018-01-25 13:00:00.00016391410
45979221392018-01-25 12:00:00.00019453110
45979231502018-01-25 12:00:00.00016390660
459796612018-01-25 12:00:00.00010289340
459728812018-01-25 11:00:00.00010288760
45973021392018-01-25 11:00:00.00019452500
45973031502018-01-25 11:00:00.00016390010
459653612018-01-25 10:00:00.00010288040
45965501392018-01-25 10:00:00.00019451800
45965511502018-01-25 10:00:00.00016389280
459584312018-01-25 09:00:00.00010287370
45958571392018-01-25 09:00:00.00019451120
45958581502018-01-25 09:00:00.00016388560
459514912018-01-25 08:00:00.00010287090
45951631392018-01-25 08:00:00.00019450440
45951641502018-01-25 08:00:00.00016387860
459439712018-01-25 07:00:00.00010286990
45944111392018-01-25 07:00:00.00019449750
45944121502018-01-25 07:00:00.00016387160
459370212018-01-25 06:00:00.00010286910
45937161392018-01-25 06:00:00.00019449140
45937171502018-01-25 06:00:00.00016386570
459294912018-01-25 05:00:00.00010286820
45929631392018-01-25 05:00:00.00019448480

I need to calculate a difference for [SummAPlus] by [DateT] for each of the CounterNumbers

so I need to compare the value of [SummAPlus]  for each [DateT]  with the previous [DateT] and calculate the difference.

I tried to do it as 

=CALCULATE(max(PowerCountersHours[SummAPlus]);FILTER(PowerCountersHours;PowerCountersHours[CounterNumber]=EARLIER(PowerCountersHours[CounterNumber]) && PowerCountersHours[DateT]<EARLIER(PowerCountersHours[DateT])))

but it doesn't work.

 

I will appreciate any help. Thanks

Need Help
  • zvm's avatar
    zvm
    8 years ago

    Great!

    Just added MinDate to have 0 in a first row per CounterNumber.

    Column = 
VAR CurrentDate = PowerCountersHours[DateT]
VAR PreviousDate =
    CALCULATE (
        MAX ( PowerCountersHours[DateT] );
        FILTER (
            ALLEXCEPT ( PowerCountersHours; PowerCountersHours[CounterNumber] );
            PowerCountersHours[DateT] < CurrentDate
        )
    )
VAR MinDate = 
    CALCULATE (
        MIN( PowerCountersHours[DateT] );
             ALLEXCEPT ( PowerCountersHours; PowerCountersHours[CounterNumber] )
          )
VAR PreviousValue = IF(PowerCountersHours[DateT] = MinDate; 
    CALCULATE (
        SUM ( PowerCountersHours[SummAPlus] );
        FILTER (
            ALLEXCEPT ( PowerCountersHours; PowerCountersHours[CounterNumber] );
            PowerCountersHours[DateT] = MinDate )
        ); 
    CALCULATE (
        SUM ( PowerCountersHours[SummAPlus] );
        FILTER (
            ALLEXCEPT ( PowerCountersHours; PowerCountersHours[CounterNumber] );
            PowerCountersHours[DateT] = PreviousDate
        )
    )
)
RETURN
    PowerCountersHours[SummAPlus] - PreviousValue

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    dkushner

     

    Try this column

     

    Column=
VAR CurrentDate = PowerCountersHours[DateT]
VAR PreviousDate =
    CALCULATE (
        MAX ( PowerCountersHours[DateT] ),
        FILTER (
            ALLEXCEPT ( PowerCountersHours, PowerCountersHours[CounterNumber] ),
            PowerCountersHours[DateT] < CurrentDate
        )
    )
VAR PreviousValue =
    CALCULATE (
        SUM ( PowerCountersHours[SummAPlus] ),
        FILTER (
            ALLEXCEPT ( PowerCountersHours, PowerCountersHours[CounterNumber] ),
            PowerCountersHours[DateT] = PreviousDate
        )
    )
RETURN
    PowerCountersHours[SummAPlus] - PreviousValue
    • zvm's avatar
      zvm
      Helper II
      8 years ago

      Great!

      Just added MinDate to have 0 in a first row per CounterNumber.

      Column = 
VAR CurrentDate = PowerCountersHours[DateT]
VAR PreviousDate =
    CALCULATE (
        MAX ( PowerCountersHours[DateT] );
        FILTER (
            ALLEXCEPT ( PowerCountersHours; PowerCountersHours[CounterNumber] );
            PowerCountersHours[DateT] < CurrentDate
        )
    )
VAR MinDate = 
    CALCULATE (
        MIN( PowerCountersHours[DateT] );
             ALLEXCEPT ( PowerCountersHours; PowerCountersHours[CounterNumber] )
          )
VAR PreviousValue = IF(PowerCountersHours[DateT] = MinDate; 
    CALCULATE (
        SUM ( PowerCountersHours[SummAPlus] );
        FILTER (
            ALLEXCEPT ( PowerCountersHours; PowerCountersHours[CounterNumber] );
            PowerCountersHours[DateT] = MinDate )
        ); 
    CALCULATE (
        SUM ( PowerCountersHours[SummAPlus] );
        FILTER (
            ALLEXCEPT ( PowerCountersHours; PowerCountersHours[CounterNumber] );
            PowerCountersHours[DateT] = PreviousDate
        )
    )
)
RETURN
    PowerCountersHours[SummAPlus] - PreviousValue