Forum Discussion
Determine progress change for same items with different date
Dear all,
I am pretty new to PowerBi and so far I could solve my problems with simple research. Now I come to a point where I could not find any solution for my question.
I have following table with values for the same ID but different dates.
| Date | ID | Some Sample Data | Progress |
| 15.01.2021 | A | whatever bla | 80% |
| 15.01.2021 | B | some other stuff | 50% |
| 15.01.2021 | C | some other stuff | 40% |
| 07.01.2021 | A | Bla | 60% |
| 07.01.2021 | B | some content | 50% |
| 07.01.2021 | C | some content | 50% |
New rows are added to this table from time to time. So in this example on 7th of Jan the first information was added. On 15th of Jan there was an update for the two IDs with updated progress.
Now I would like to figure out how the progress for the IDs has changed compared to the last update. Basically the table should then look like this after the refresh of the data:
| Date | ID | Some Sample Data | Progress | Progress previous date | Difference |
| 15.01.2021 | A | whatever bla | 80% | 60% | 20 |
| 15.01.2021 | B | some other stuff | 50% | 50% | 0 |
| 15.01.2021 | C | some other stuff | 40% | 50% | -10 |
| 07.01.2021 | A | Bla | 60% | 0 | 0 |
| 07.01.2021 | B | some content | 50% | 0 | 0 |
| 07.01.2021 | C | some content | 50% | 0 | 0 |
How would I do this during transformation with the power query language?
Or is this rather something that I would do using DAX? How?
Appreciate your help on this.
Regards
Hans
Hi TheHans ,
You can also use below dax expressions:
Progress previous date = var _date=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date]<EARLIER('Table'[Date]))) Return IF(_date=BLANK(),0,CALCULATE(MAX('Table'[Progress]),FILTER('Table','Table'[Date]=_date&&'Table'[ID]=EARLIER('Table'[ID]))))difference = IF('Table'[Progress previous date]=0,0, 'Table'[Progress]-'Table'[Progress previous date])And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
6 Replies
You can do it in either place. Ask yourself if the result can be influenced by user interaction with your reports (setting an additional date filter, for example). If the answer is no then you can do it in Power Query or in a calculated column, otherwise you need to create a DAX measure. I think the latter is more likely - please confirm.
Search for the "previous row" pattern, there are lots of examples on how to do that.
Thanks for the fast reply .
Then I guess it would be rather DAX in order to remain flexible.
I basically would like to show the progress per ID compared to the last date for the same ID.
How would I do that?
Regards
Hans
Hi TheHans ,
You can also use below dax expressions:
Progress previous date = var _date=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date]<EARLIER('Table'[Date]))) Return IF(_date=BLANK(),0,CALCULATE(MAX('Table'[Progress]),FILTER('Table','Table'[Date]=_date&&'Table'[ID]=EARLIER('Table'[ID]))))difference = IF('Table'[Progress previous date]=0,0, 'Table'[Progress]-'Table'[Progress previous date])And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- Progress Previous Date =VAR d =MAX ( Progress[Date] )VAR p =CALCULATE (MAX ( Progress[Date] ),ALLEXCEPT ( Progress, Progress[ID] ),Progress[Date] < d)RETURNCALCULATE (MAX ( Progress[Progress] ),ALLEXCEPT ( Progress, Progress[ID] ),Progress[Date] = p)
Hi Ilbendlin,
the value in each column is the difference between the most uptodate value compared with the first value.
So lets say for ID A we do have in total three values with three dates, the difference is the value of the last one compared to the first one. Means when in the first date the progress was 0, second date progress 25% and third date progress 75% the right value should be 50% (75-25). However with this formula the difference is 75% (75-0).
