Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dkushner
Frequent Visitor

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

1 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

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

@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

Regards
Zubair

Please try my custom visuals

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
Not applicable

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
Frequent Visitor

Thanks!!!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.