Forum Discussion
Helper IIAccumulated Time by ID
Guys,
I have the following table, let's call it "stage_history".
I want to calculate the time between the column, ordered by "DEAL_ID", from the first (min) date_create to the last (max) date_create, and as soon as another deal_id appears, it needs to restart.
Trying to make it clear:
I need to order (not group) by DEAL_ID, from the earliest to the latest, calculated the difference of time, and restart the calculation when a new DEAL_ID appears.
How can I do it?
Hi, tahechadv_2022
You can try the following methods. The first step starts with adding the index column to the Power Query.
Column = CALCULATE(COUNT('Table'[DEAL_ID]),FILTER(ALLEXCEPT('Table','Table'[DEAL_ID]),[Index]<=EARLIER('Table'[Index])))Is this the result you expect? If not, please let us know what output you expect.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
v-zhangtiCommunity Support
Hi, tahechadv_2022
You can try the following methods. The first step starts with adding the index column to the Power Query.
Column = CALCULATE(COUNT('Table'[DEAL_ID]),FILTER(ALLEXCEPT('Table','Table'[DEAL_ID]),[Index]<=EARLIER('Table'[Index])))Is this the result you expect? If not, please let us know what output you expect.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ryan_mayuSuper User
here is a workaround for you
1. create an index column in PQ
2. use DAX to create two columns
Column = VAR _last=maxx(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1),'Table'[DEAL_ID]) return if('Table'[DEAL_ID]<>_last,1,0) order = sumx(FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])),'Table'[Column])pls see the attachment below