How to identify a new job in Power BI (with starting value 0)

[ymobbs]

Hi,

I have a task in my monthly report. I need to identify new jobs in the forecast sheet. 

Our forecast sheet looks like this:

Customer	Project	1-Nov-19	1-Dec-19	1-Jan-20	1-Feb-20	1-Mar-20	1-Apr-20
Customer 1	Project 1	0	0	100	100	100	0
Customer 1	Project 2	100	100	100	100	0	0
Customer 1	Project 3	100	100	100	200	200	300
Customer 2	Project 4	300	400	100	100	100	100
Customer 2	Project 5	0	100	200	0	0	0
Customer 3	Project 6	0	0	0	150	0	0
Customer 4	Project 7	0	0	0	0	0	0

I need a measure to identify new jobs. In the table above, Customer 1 has a new Project 1, Customer 2 has a new Project 5, and Customer 3 has a new Project 6. Because their volume is 0 in the starting month/months. It doesn't matter how many months a customer has 0 volume. As long as it has 0 in the beginning month/months, we treat it as a new job. However, if a customer has 0 in all months, it's not a new job, i.e., Customer 4's Project 7 is not a new job.

I'm also struggling of where to place this result (Y/N of new job) in the table. I can put Project number as a drilldown under Customer in the Rows. And the month is placed in the Columns. 

Have been struggling on this issue for a while. Anybody?

Thank you in advance!!!!

Regards, 

[v-eachen-msft · Accepted solution]

Hi @ymobbs ,

You could unpivot your date columns and create a new date column.

Then you could create two measures to get "Y/N" and use matrix visual to show the result.

Measure =
VAR a =
    CALCULATE (
        MIN ( 'Table'[Attribute] ),
        ALLEXCEPT ( 'Table', 'Table'[Project] )
    )
VAR c =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Attribute] = a )
    )
VAR b =
    CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Project] ) )
RETURN
    IF ( c = 0 && b <> 0, "Y", "N" )

Value measure =
IF (
    NOT ( ISINSCOPE ( 'Table'[Attribute] ) ),
    [Measure],
    SELECTEDVALUE ( 'Table'[Value] )
)

Here is the result.

Here is my test file for your reference.

[ymobbs]

Thank you very much! It works!

@v-eachen-msft