Order to order growth rate per customer

[AlexF_HH]

Hi PowerBi community!

It's a common case to calculate growth rates based on month, quorter, year.
My task is to calculate the growth rates between orders regardles the time frame.  

Let's assume the follwing simplified data table:



ID: is salesorder ID
acc.id: is client
so.created: is order created date
so.buget: is the order amount
avg.budget; is order omount / order duration

What i want to do is to find out the growth rate between the last order and the order before the last order for each client.

For exmaple:
Client A ordered 40.000 on 01.01.20 and 6.666 on 01.01.21. The growth rate would be ca. -84%

My approach with my real data table was to find the date of the order before the last order first.
(And then to claculate the reset i need) 

However, i just don't get it. That's what i'm trying to do...

so.old.created = 
CALCULATE(
    
    MAX(salesorders[so.created].[Date])
    ,
    FILTER(ALL('salesorders'),
    'salesorders'[so.acc.id] = SELECTEDVALUE(salesorders[so.acc.id])
    &&
    salesorders[so.created].[Date] < SELECTEDVALUE(salesorders[so.created].[Date])

)

)

Here i just get the latest order created date in every single order row.


Do you have any suggstions or maybe a link to a similar problem?

Thank you

[AlexF_HH]

@Greg_Deckler 

Hi,

thank you very much for your help and the interesting MTBF approach you suggest.
I was not able to solve the task using just the basic patern. 

Instead i gave one more try to my own take on that. I did two steps:

1. Identfied the order date before the last order date.

so.old.created = 
CALCULATE(
    
    MAX('salesorder'[so.created])
    ,
    FILTER(ALL('salesorder'),
    'salesorder'[acc.id] = SELECTEDVALUE('salesorder'[acc.id])
    &&
    'salesorder'[so.created] < SELECTEDVALUE('salesorder'[so.created])

)

)

2. And then i identified the budget for the order nefor ethe last order (using the above measure as my filter)

so.old.avg.budget = 

VAR _oldcreated = salesorder[so.old.created]

RETURN

CALCULATE(
    
    MAX('salesorder'[avg.budget])
    ,
    FILTER(ALL('salesorder'),
    'salesorder'[acc.id] = SELECTEDVALUE('salesorder'[acc.id])
    &&
    'salesorder'[so.created] = _oldcreated

)

)

And that's my result:

I'm not sure if my solution is the most effective one. Still need to make it work in my production envorinoment.
For some reason it takes ages to calculate there (with ca. 10k items).

(Anyway, i'll have to dig deeper into your approach and maybe i will figure it out in the end.)

Best

[Greg_Deckler]

@AlexF_HH Our approaches are essentially the same. You are using CALCUALTE and MAX and I am using MAXX and FILTER. Two different methods of accomplishing the same thing and both involve first "looking up" the last/previous "thing" based on some consequetive identifier (date / id / index) where you are grabbing the max value of that thing that is less than the current thing. Then you use that to "lookup" the value you want for that item. 

[AlexF_HH]

@Greg_Deckler 

Hi,

now i adopted your method using a calculated column with MAXX and FILTER instead of my Measure approch with CALCULATE and MAX. It's a huge performnace improvement 🙂

Thanks again.

[Greg_Deckler · Accepted solution]

@AlexF_HH You want to get the current value of Date in a VAR. Then use FILTER(ALL('Table'), [Date] < __VariableName). Basically it is the MTBF pattern:

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....
The basic pattern is:
Column = 
  VAR __Current = MAX([Value])

  VAR __Date = MAX([Date])
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < __Date),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous