cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

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

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?

Regards,

1 ACCEPTED SOLUTION
Community Support

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.

If this post helps, then please consider Accept it as the solution to help the other members find it.
2 REPLIES 2
Community Support

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.

If this post helps, then please consider Accept it as the solution to help the other members find it.
Helper I

Thank you very much! It works!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors