Include ID with no rows in GL for historical table

[Anonymous]

Hi,

I'm struggling to find a way to include a project ID that does not have any value (or rows) in my GL table, where I get revenue data from. The table I'm trying to create needs to show the selected period status type for each project. This is why I have created my measure with LASTNONBLANK. The status type can change from one period to another. 
My issue is that I would like to include also projects (in this case 10018) that does not have any records in my GL table, but exists with a status in my historical project list. As you can see, my measure has a + 0 at the end in hope to include 10018. But it ends up showing too many records and wrong because I've added the 0. Without adding + 0, only 10058 shows, which is correct, but would like to have it as shown in my very right table, both of them with correct status type.

Any suggestions on how to solve this?

Thank you so much!

[lbendlin]

Your data is missing an index column that would allow to identify what "latest"  means for each project and type. Without that LASTNONBLANK() is rather meaningless.

[Anonymous]

@lbendlin  Doesn't the date slicer filter work as the index? Or am I missing something here?

[lbendlin]

Look at the first three rows of your sample data. How do you know which row to pick for the desired outcome?

[Anonymous]

@lbendlin , The first row with Type "Active". This is the only row that exists with date 02/21. The other two are added to this table once I use + 0 in my measure to include project 10018. Each project has a status type in each period, which means that I want it to look for the status matching the month selected.

[lbendlin]

What if you have more than one active row per project? How do you define "first" in that case?

[Anonymous]

How can I have more than one active row per project? The combination of project and period is unique.

[lbendlin]

In your table visual enable "Show items with no value"