calculating difference with the previous value

[dkushner]

Hello everybody,

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

I have a table like

ID	CounterNumber	DateT	SummAPlus
4598661	1	2018-01-25 13:00:00.000	10289870
4598675	139	2018-01-25 13:00:00.000	19453840
4598676	150	2018-01-25 13:00:00.000	16391410
4597922	139	2018-01-25 12:00:00.000	19453110
4597923	150	2018-01-25 12:00:00.000	16390660
4597966	1	2018-01-25 12:00:00.000	10289340
4597288	1	2018-01-25 11:00:00.000	10288760
4597302	139	2018-01-25 11:00:00.000	19452500
4597303	150	2018-01-25 11:00:00.000	16390010
4596536	1	2018-01-25 10:00:00.000	10288040
4596550	139	2018-01-25 10:00:00.000	19451800
4596551	150	2018-01-25 10:00:00.000	16389280
4595843	1	2018-01-25 09:00:00.000	10287370
4595857	139	2018-01-25 09:00:00.000	19451120
4595858	150	2018-01-25 09:00:00.000	16388560
4595149	1	2018-01-25 08:00:00.000	10287090
4595163	139	2018-01-25 08:00:00.000	19450440
4595164	150	2018-01-25 08:00:00.000	16387860
4594397	1	2018-01-25 07:00:00.000	10286990
4594411	139	2018-01-25 07:00:00.000	19449750
4594412	150	2018-01-25 07:00:00.000	16387160
4593702	1	2018-01-25 06:00:00.000	10286910
4593716	139	2018-01-25 06:00:00.000	19449140
4593717	150	2018-01-25 06:00:00.000	16386570
4592949	1	2018-01-25 05:00:00.000	10286820
4592963	139	2018-01-25 05:00:00.000	19448480

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

[Zubair_Muhammad]

@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 · Accepted solution]

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

[Anonymous]

How would I write the column if I wanted to quantify the cases of improvement/worseing compared to the previous period.

In other words I want a bar chart where the axis is "improved", "worsened" "stayed the same". The value should count the cases with these categories. However, I of course just wanna take into account the latest change. Not the change from, let's say, 3 months ago compared to 4 months ago.

[dkushner]

Thanks!!!