Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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