Correlating data across multiple tables for the last known value

Question

I am working in Power BI to create a visualization about project reviews and statuses. I'm using Dax to try to accomplish two things.

I've done some research and it seems I should be using the last known value, but I keep getting a circular reference.

First, projects have checkpoint reviews before proceeding to the next evolution. This may happen once a year, or multiple times a year. Projects are also reporting status on a project, quarterly if they're performing great or good, and monthly if average or underperforming. The frequency will change if a project goes from good to average and vice versa.

For each review, I want to correlate whatever the last known reported status for a project is.

Correlating Project Review to Status and Using last known for new column

I used this code to bring in the project status data point I have into my review table

Overall Status =
LOOKUPVALUE (
    'Project Status'[Status],
    'Project Status'[Project Title], 'Review'[Project Title],
    'Project Status'[YearMonth], 'Review'[YearMonth],
    'Project Status'[Attribute], "Overall",
    ""
)

This only brings in those items that match so I then created a new column with the following

New Overall =
LOOKUPVALUE (
    Review[Project Overall Status],
    Review[Review Date],
        CALCULATE (
            LASTNONBLANKVALUE ( Review[Review Date], 1 ),
            FILTER (
                Review,
                Review[Project Overall Status] > 0
                    && EARLIER ( Review[Review Date] ) >= Review[Review Date]
            )
        )
)

This is where I get the circular reference. Ideally, I'd like to get the status from the project status table. I was trying to use this as a workaround. If I can resolve the circular that will work, but ideally the end state would just use the last known status from the project status table.

Ideal end state for correlation project status to reviews

The second thing I want to do is create a table that uses the last known project status like this:

Last Known Project Status

I have created a date table for 2020, but I am unsure how to properly use it. There are a lot of resources on how to create a date table, but I didn't find too many that showed how to use it in a meaningful way.

What do you mean by "the circular"? E.g., is a word missing? — Peter Mortensen
The exact error provided says: A circular dependency was detected: Review [Overall Status New]. — ldc9819

Alexis Olson Alexis Olson · Accepted Answer · 2020-11-10T15:14:49

First, calculate the last Year Month for that project and then use that value in your lookup:

New Overall = 
VAR CurrProject = Review[Project]
VAR CurrYearMonth = Review[Year Month]
VAR LastYearMonth =
    CALCULATE (
        MAX ( 'Project Status'[Year Month] ),
        'Project Status'[Project Title] = CurrProject,
        'Project Status'[Year Month] <= CurrYearMonth
    )
RETURN
    LOOKUPVALUE (
        'Project Status'[Status],
        'Project Status'[Attribute], "Overall",
        'Project Status'[Year Month], LastYearMonth,
        'Project Status'[Project Title], CurrProject
    )

Correlating data across multiple tables for the last known value

1 Answers