Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to create a formula that uses a lookupvalue function within a measure (Which might not be the best way to solve my problem). What I am trying to do is create a measure that finds the maximum Valid_From_Dttm prior to 5/1/2108 for a specific project, where Project_Milestone_Name = Project Mature. Based upon that resulting value/row, I then want my measure to lookup the corresponding "Report Date". Below is an image of the table I am working with and the Dax formulas I have written so far. It seem like it should be working using a measure and then a column based upon the measure, but the column that is based upon the measure returns a circular dependency error (Which makes sense since I'm not telling it what to calculate first).
Measure:
Max Mature Valid From Date = CALCULATE(MAXX('SUV Project_Critical_Milestone_Progression','SUV Project_Critical_Milestone_Progression'[Valid_From_Dttm]),'SUV Project_Critical_Milestone_Progression'[Project_Milestone_Name] = "Project Mature", 'SUV Project_Critical_Milestone_Progression'[Valid_From_Dttm] < DATE(2018,05,01))
Column:
Mature Report Date = LOOKUPVALUE('SUV Project_Critical_Milestone_Progression'[Report Date], 'SUV Project_Critical_Milestone_Progression'[Valid_From_Dttm],[Max Mature Valid From Date])
Here is the error message that I get back for the column:
A circular dependency was detected: SUV Project_Critical_Milestone_Progression[Column], 126c400b-8ac4-4010-9adb-0ad3590f4118, SUV Project_Critical_Milestone_Progression[Column].
Thanks in advance for any help and please let me know if my question is not clear.
-Alex
Solved! Go to Solution.
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
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
Sam,
Thank you for the response, that post solved my problem!
I created a measure using the following:
Mature Date Prior to May 1 2018 = MAXX(TOPN(1, FILTER('SUV Project_Critical_Milestone_Progression', 'SUV Project_Critical_Milestone_Progression'[Project_Milestone_Name] ="Project Mature" && 'SUV Project_Critical_Milestone_Progression'[Valid_From_Dttm] < DATE(2018,05,10)), 'SUV Project_Critical_Milestone_Progression'[Valid_From_Dttm],DESC), 'SUV Project_Critical_Milestone_Progression'[Report Date])
@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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |