Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
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.
Thanks!!!
User | Count |
---|---|
103 | |
87 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |