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

Reply
jeyare
Helper II
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:

datepersonperformance
4.4.2020A10
4.4.2020B12
4.4.2020C8
4.4.2020D15
5.4.2020A12
5.4.2020B16
5.4.2020C10
5.4.2020D5
6.4.2020A9
6.4.2020B8
6.4.2020C11
6.4.2020D7
7.4.2020A6
7.4.2020B4
7.4.2020C20
7.4.2020D11


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

datepersonperformanceIncrement
4.4.2020A100
4.4.2020B120
4.4.2020C80
4.4.2020D150
5.4.2020A122
5.4.2020B164
5.4.2020C102
5.4.2020D5-10
6.4.2020A9-3
6.4.2020B8-8
6.4.2020C111
6.4.2020D72
7.4.2020A6-3
7.4.2020B4-4
7.4.2020C209
7.4.2020D114

 

Thx for a help.

J.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@jeyare ,

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)

Greg_Deckler
Super User
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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

my reply was for @Greg_Deckler 

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

thx @Greg_Deckler , my fault

No worries! Glad we got it solved! 🙂

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
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...


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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