Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Team,
I am looking for a DAX formula that looks up the most recent previous profit figure of a project. The dates are not sequentual. This has been covered in a previous post linked below but my data set includes profit from multiple projects. I am looking to see the previous profit from the same project. Data looks like this:
Project | Date | Profit |
222222 | 15/09/2022 | $ 1,500.00 |
222222 | 15/10/2022 | $ 1,500.00 |
111222 | 1/11/2022 | $ 100.00 |
8787878 | 14/11/2022 | $ 50.00 |
111222 | 15/11/2022 | $ 100.00 |
222222 | 15/11/2022 | $ 1,500.00 |
8787878 | 24/11/2022 | $ 100.00 |
8787878 | 11/12/2022 | $ 110.00 |
222222 | 15/12/2022 | $ 500.00 |
111222 | 21/12/2022 | $ 120.00 |
8787878 | 23/12/2022 | $ 190.00 |
222222 | 15/01/2023 | $ 500.00 |
8787878 | 15/01/2023 | $ 555.00 |
111222 | 1/03/2023 | $ 120.00 |
I have manually generated the expected results in the following table:
Project | Date | Profit | Previous |
222222 | 15/09/2022 | $ 1,500.00 | |
222222 | 15/10/2022 | $ 1,500.00 | 1500 |
111222 | 1/11/2022 | $ 100.00 | |
8787878 | 14/11/2022 | $ 50.00 | |
111222 | 15/11/2022 | $ 100.00 | 100 |
222222 | 15/11/2022 | $ 1,500.00 | 1500 |
8787878 | 24/11/2022 | $ 100.00 | 50 |
8787878 | 11/12/2022 | $ 110.00 | 100 |
222222 | 15/12/2022 | $ 500.00 | 1500 |
111222 | 21/12/2022 | $ 120.00 | 100 |
8787878 | 23/12/2022 | $ 190.00 | 110 |
222222 | 15/01/2023 | $ 500.00 | 500 |
8787878 | 15/01/2023 | $ 555.00 | 190 |
111222 | 1/03/2023 | $ 120.00 | 120 |
Here is a link to a similar question:
https://community.powerbi.com/t5/Desktop/Previous-Value-Using-DAX/m-p/933746
Thanks in advance.
Solved! Go to Solution.
Slight adjustment to the last part of the formula.
Column =
VAR _last=maxx(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
return if(ISBLANK(_last),blank(),maxx(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[Date]=_last),'Table'[Profit]))
you can try this
Column =
VAR _last=maxx(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
return if(ISBLANK(_last),blank(),maxx(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[Date]=_last),'Table'[ Profit ]))
Proud to be a Super User!
Slight adjustment to the last part of the formula.
Column =
VAR _last=maxx(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])
return if(ISBLANK(_last),blank(),maxx(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[Date]=_last),'Table'[Profit]))