cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

 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

1 ACCEPTED SOLUTION
Helper II

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 4
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

Helper II

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.

Frequent Visitor

Thanks!!!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors