Measure with Lookupvalue?

[akov]

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

[v-chuncz-msft · Accepted solution]

@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]

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])

[Anonymous]

@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)

[akov]

@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