cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

Increment of previous date value

Hi,

can't find a solution for callculation of increments based on few conditions.

Current table example in my Power Query:

 date person performance 4.4.2020 A 10 4.4.2020 B 12 4.4.2020 C 8 4.4.2020 D 15 5.4.2020 A 12 5.4.2020 B 16 5.4.2020 C 10 5.4.2020 D 5 6.4.2020 A 9 6.4.2020 B 8 6.4.2020 C 11 6.4.2020 D 7 7.4.2020 A 6 7.4.2020 B 4 7.4.2020 C 20 7.4.2020 D 11

Expected solution:

I need calculate the increment of column "performance" based on previous date value include column "person" evaluation:

date = 4.4.2020 for person = A  there is performance = 10

date = 5.4.2020 for person = A  there is performance = 12

then increment is = 2

 date person performance Increment 4.4.2020 A 10 0 4.4.2020 B 12 0 4.4.2020 C 8 0 4.4.2020 D 15 0 5.4.2020 A 12 2 5.4.2020 B 16 4 5.4.2020 C 10 2 5.4.2020 D 5 -10 6.4.2020 A 9 -3 6.4.2020 B 8 -8 6.4.2020 C 11 1 6.4.2020 D 7 2 7.4.2020 A 6 -3 7.4.2020 B 4 -4 7.4.2020 C 20 9 7.4.2020 D 11 4

Thx for a help.

J.

1 ACCEPTED SOLUTION
Super User

So basically, as in the article referenced, something like:

``````Increment Column =
VAR __Current = [performance]
VAR __PreviousDate = MAXX(FILTER('Table',[person] = EARLIER([person]) && [date] < EARLIER([date])),[date])
VAR __Previous = MAXX(FILTER('Table',[person] = EARLIER([person]) && [date] = __PreviousDate),[performance])
RETURN
__Current - __Previous``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
8 REPLIES 8
Super User

Try like

diff =
var _last = maxx(filter(Table,table[person]=earlier(table[person]) && table[Date] = earlier(table[Date])-1),table[performance])
return
if(isblank(_last),0,performance-_last)

Super User

So basically, as in the article referenced, something like:

``````Increment Column =
VAR __Current = [performance]
VAR __PreviousDate = MAXX(FILTER('Table',[person] = EARLIER([person]) && [date] < EARLIER([date])),[date])
VAR __Previous = MAXX(FILTER('Table',[person] = EARLIER([person]) && [date] = __PreviousDate),[performance])
RETURN
__Current - __Previous``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

thx, seems to be fine until first row, when I get an error:

The value for 'performance' cannot be determined. Either 'performance' doesn't exist, or there is no current row for a column named 'Tperformance'.

Then IF needs to be there, but not sure where

Helper II

Super User

Hmm, see attached PBIX. Can't say what went wrong but the PBIX seems to work fine.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

thx @Greg_Deckler , my fault

Super User
No worries! Glad we got it solved! 🙂

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...