Forum Discussion
Measure with Lookupvalue?
akov,
You may take a look at the following post.
https://community.powerbi.com/t5/Desktop/Find-last-value-based-on-date/m-p/293150#M129228
akov Can you post an excel sheet with dummy data for this problem? Also why are you not creating the 2nd column as a measure.
In the mean time try using an if statement comparing max date with from date and give the report date as output if true.
IF(MaxDate = FromDate,ReportDate,NULL)
Anonymous
Thank you for your response! Please see the dummy data below (I can't figure out how to attach a file). Table one contains the Data with the dates and milestones and table two contains the Single Project Name and Project _SK. Table one is joined in the model, many to one, to table two.
To be clear, what I am trying to do is create a report that shows the project name and then the Report date for the "Project Mature" milestone that has the Max Date prior to May 1, 2018.
Something likes this
Project Name Project Mature Date (Prior to May 1 2018)
Project 1 Null
Project 2 Null
Project 4 2/28/2019
Project 6 5/31/2010
I tried the formula you provided below, but it gave me the same error about a circular dependency.
Table 1
| Project_Name | Project_SK | Project_Milestone_Name | Estimated_Date | Actual_Date | progression | Valid_From_Dttm | Valid_To_Dttm | Report Date |
| Project 1 | 264 | Material Permits Non-Appealable | NA | 6/21/2018 13:22 | ||||
| Project 1 | 264 | Project Mature | NA | 6/21/2018 13:22 | ||||
| Project 1 | 264 | Site Secured | NA | 6/21/2018 13:22 | ||||
| Project 2 | 191 | Material Permits Non-Appealable | NA | 4/24/2018 17:06 | ||||
| Project 2 | 191 | Project Mature | NA | 4/24/2018 17:06 | ||||
| Project 2 | 191 | Site Secured | NA | 4/24/2018 17:06 | ||||
| Project 3 | 200 | Material Permits Non-Appealable | NA | 4/24/2018 17:06 | ||||
| Project 3 | 200 | Project Mature | NA | 4/24/2018 17:06 | ||||
| Project 3 | 200 | Site Secured | NA | 4/24/2018 17:06 | ||||
| Project 4 | 57 | Material Permits Non-Appealable | 10/24/2018 0:00 | Not Started | 4/24/2018 17:06 | 4/30/2018 16:08 | 10/24/2018 0:00 | |
| Project 4 | 57 | Material Permits Non-Appealable | 10/23/2018 0:00 | Not Started | 4/30/2018 16:08 | 5/7/2018 10:38 | 10/23/2018 0:00 | |
| Project 4 | 57 | Material Permits Non-Appealable | 10/24/2018 0:00 | Not Started | 5/7/2018 10:38 | 6/22/2018 13:42 | 10/24/2018 0:00 | |
| Project 4 | 57 | Material Permits Non-Appealable | 3/1/2019 1:00 | Not Started | 6/22/2018 13:42 | 3/1/2019 1:00 | ||
| Project 4 | 57 | Project Mature | 3/1/2019 0:00 | In Progress | 4/24/2018 17:06 | 4/30/2018 16:08 | 3/1/2019 0:00 | |
| Project 4 | 57 | Project Mature | 2/28/2019 0:00 | In Progress | 4/30/2018 16:08 | 5/7/2018 10:38 | 2/28/2019 0:00 | |
| Project 4 | 57 | Project Mature | 3/1/2019 0:00 | In Progress | 5/7/2018 10:38 | 3/1/2019 0:00 | ||
| Project 4 | 57 | Site Secured | 9/15/2017 0:00 | In Progress | 4/24/2018 17:06 | 4/30/2018 16:08 | 9/15/2017 0:00 | |
| Project 4 | 57 | Site Secured | 9/14/2017 0:00 | In Progress | 4/30/2018 16:08 | 5/7/2018 10:38 | 9/14/2017 0:00 | |
| Project 4 | 57 | Site Secured | 9/15/2017 0:00 | In Progress | 5/7/2018 10:38 | 5/23/2018 0:05 | 9/15/2017 0:00 | |
| Project 4 | 57 | Site Secured | 7/1/2018 0:00 | In Progress | 5/23/2018 0:05 | 7/1/2018 0:00 | ||
| Project 5 | 47 | Material Permits Non-Appealable | NA | 4/24/2018 17:06 | ||||
| Project 5 | 47 | Project Mature | NA | 4/24/2018 17:06 | ||||
| Project 5 | 47 | Site Secured | NA | 4/24/2018 17:06 | ||||
| Project 6 | 62 | Material Permits Non-Appealable | 6/4/2019 0:00 | Not Started | 4/24/2018 17:06 | 4/30/2018 16:08 | 6/4/2019 0:00 | |
| Project 6 | 62 | Material Permits Non-Appealable | 6/3/2019 0:00 | Not Started | 4/30/2018 16:08 | 5/7/2018 10:38 | 6/3/2019 0:00 | |
| Project 6 | 62 | Material Permits Non-Appealable | 6/4/2019 0:00 | Not Started | 5/7/2018 10:38 | 6/4/2019 0:00 | ||
| Project 6 | 62 | Project Mature | 6/1/2020 0:00 | Not Started | 4/24/2018 17:06 | 4/30/2018 16:08 | 6/1/2020 0:00 | |
| Project 6 | 62 | Project Mature | 5/31/2020 0:00 | Not Started | 4/30/2018 16:08 | 5/7/2018 10:38 | 5/31/2020 0:00 | |
| Project 6 | 62 | Project Mature | 6/1/2020 0:00 | Not Started | 5/7/2018 10:38 | 6/1/2020 0:00 | ||
| Project 6 | 62 | Site Secured | 12/28/2018 0:00 | In Progress | 4/24/2018 17:06 | 4/30/2018 16:08 | 12/28/2018 0:00 | |
| Project 6 | 62 | Site Secured | 12/27/2018 0:00 | In Progress | 4/30/2018 16:08 | 5/7/2018 10:38 | 12/27/2018 0:00 | |
| Project 6 | 62 | Site Secured | 12/28/2018 0:00 | In Progress | 5/7/2018 10:38 | 12/28/2018 0:00 | ||
| Project 7 | 175 | Material Permits Non-Appealable | NA | 4/24/2018 17:06 | ||||
| Project 7 | 175 | Project Mature | NA | 4/24/2018 17:06 | ||||
| Project 7 | 175 | Site Secured | NA | 4/24/2018 17:06 | ||||
| Project 8 | 65 | Material Permits Non-Appealable | 2/1/2019 0:00 | In Progress | 6/15/2018 13:02 | 2/1/2019 0:00 | ||
| Project 8 | 65 | Material Permits Non-Appealable | 2/1/2019 0:00 | Not Started | 4/24/2018 17:06 | 4/30/2018 16:08 | 2/1/2019 0:00 | |
| Project 8 | 65 | Material Permits Non-Appealable | 1/31/2019 0:00 | Not Started | 4/30/2018 16:08 | 5/7/2018 10:38 | 1/31/2019 0:00 | |
| Project 8 | 65 | Material Permits Non-Appealable | 2/1/2019 0:00 | Not Started | 5/7/2018 10:38 | 6/15/2018 13:02 | 2/1/2019 0:00 | |
| Project 8 | 65 | Project Mature | 1/30/2019 0:00 | In Progress | 4/24/2018 17:06 | 4/30/2018 16:08 | 1/30/2019 0:00 | |
| Project 8 | 65 | Project Mature | 1/29/2019 0:00 | In Progress | 4/30/2018 16:08 | 5/7/2018 10:38 | 1/29/2019 0:00 | |
| Project 8 | 65 | Project Mature | 1/30/2019 0:00 | In Progress | 5/7/2018 10:38 | 1/30/2019 0:00 | ||
| Project 8 | 65 | Site Secured | 4/10/2018 0:00 | Complete | 6/15/2018 13:02 | 6/19/2018 17:42 | 4/10/2018 0:00 | |
| Project 8 | 65 | Site Secured | 3/2/2018 1:00 | 5/15/2018 6:00 | Complete | 6/19/2018 17:42 | 5/15/2018 6:00 | |
| Project 8 | 65 | Site Secured | 4/10/2018 0:00 | In Progress | 4/24/2018 17:06 | 4/30/2018 16:08 | 4/10/2018 0:00 | |
| Project 8 | 65 | Site Secured | 4/9/2018 0:00 | In Progress | 4/30/2018 16:08 | 5/7/2018 10:38 | 4/9/2018 0:00 | |
| Project 8 | 65 | Site Secured | 4/10/2018 0:00 | In Progress | 5/7/2018 10:38 | 6/15/2018 13:02 | 4/10/2018 0:00 |
Table 2
| Project Name | Project_SK |
| Project 1 | 264 |
| Project 2 | 191 |
| Project 3 | 200 |
| Project 4 | 57 |
| Project 5 | 47 |
| Project 6 | 62 |
| Project 7 | 175 |
| Project 8 | 65 |