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 ID CounterNumber DateT SummAPlus 4598661 1 2018-01-25 13:00:00.000 1028987...
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
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
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.