Determine If Project Is New, Existing Or Closed

[Anonymous]

This is really a head-scratcher...

I have a report in a table "Pbi" that will have new rows appended on a weekly basis. This table contains data related to the projects. As of today, there are three reports (three values in the Source Data column).

What I want to do is to determine which projects are New, Closed, Ongoing.

New is the project that was is included in the previous report*

Closed is the project that is not included in the new report

Ongoing are all other projects

Example

1) Project P20020013.02 is included in the following reports as of 04/12/2020 & 11/12/2020. Status for the row with this project number and Source Date = 04/12/2020 should be New. For the same project, but with Source Date = 11/12/2020, the status should be Ongoing. 

2) Project P1930181 is included in the following reports as of 25/11/2020 & 04/12/2020. Status for the row with this project number and Source Date = 25/11/2020 should be New. For the same project, but with Source Date = 04/12/2020, the status should be Ongoing. 

3) However, I am not sure how we can deal with Closed projects. Ideally, I would like to have a walk (waterfall) that will show Ongoing Projects+New Projects-Closed Projects=Current Value...

I was trying to work with lookupvalue, but it doesn't make much sense...

Here is the dataset:

https://drive.google.com/file/d/1m906gbRyhl6DBlt5BwyN4qX8YuGDfbNS/view?usp=sharing

thanks

[Anonymous]

Hello @AlB 

Thank you, we are getting there. It shows correctly projects that are NEW and ONGOING but doesn't show any projects that are closed, but I think I know the reason.

For example P18060042 for the row with Source.Name = 04/12/2020 is actually both...

Ongoing (because was already in Source.Name = 27/11/2020 and is in 04/12/2020),

Closed (because doesn't appear on 11/12/2020)

My ultimate idea as mentioned earlier, is to make a waterfall chart... would it be possible? I guess I would need to change the logic somehow. Or maybe a summarized table?

[AlB]

@Anonymous 

You'll have to explain a bit more what you exactly want on the waterfall chart

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[v-yiruan-msft]

Hi @Anonymous ,

I updated your sample pbix file, please check whether that is what you want. I still create the measure...

Best Regards

[AlB · Accepted solution]

Hi @Anonymous 

Try this for a calculated column in your Pbi table:

New Col =
VAR currentDate_ = Pbi[Source.Name]
VAR latestDate_ =
    CALCULATE ( MAX ( Pbi[Source.Name] ), ALLEXCEPT ( Pbi, Pbi[Project No.] ) )
VAR earliestDate_ =
    CALCULATE ( MIN ( Pbi[Source.Name] ), ALLEXCEPT ( Pbi, Pbi[Project No.] ) )
RETURN
    SWITCH (
        TRUE (),
        latestDate_ < currentDate_, "Closed",
        earliestDate_ = currentDate_, "New",
        "Ongoing"
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[v-yiruan-msft]

Hi @Anonymous ,

You can create a measure as below:

Status for project = 
VAR _precount =
    CALCULATE (
        COUNT ( 'Pbi'[Project No.] ),
        FILTER (
            ALL ( 'Pbi' ),
            'Pbi'[Source.Name] <MAX ( 'Pbi'[Source.Name] )
                && 'Pbi'[Project No.] = MAX ( 'Pbi'[Project No.] )
        )
    )
RETURN
    IF ( ISBLANK ( _precount ), "New", "Ongoing" )

The above formula only judges new and ongoing projects, not sure how you want to judge which project is closed. For example, P16120006, P18040022.01 and P18110005, they only appeared in source name with "27/11/2020" , but no longer appeared on a later dates. Are these projects judged to be closed?

Best Regards

[Anonymous]

Hi @v-yiruan-msft 

Sorry, I didn't get a notification that someone replied. Yes - it works brilliantly as a measure.

However, I wanted to get this into a calculated column if possible. My idea was to create a walk (waterfall chart) to show the values of opening + new - closed = new report.

To your question - yes, if the project does not appear on further dates, it means that it has been closed. Do you have an idea of how to put this into the formula as well?