cancel
Showing results for
Did you mean:
Helper II

## Help with Measure to calculate Net new sales

Hello,

I am looking to create a net new number and attainment/Linearity calculation with our sales data. The reasoning behind this need is that every time we run into a NEWqtr, the very first day we have orders prebooked, so looking for how we are doing in the qtr is always biased because of the amount of prebooks we have.

The PBIX has a filter that links the capture date to the sales data(sales data table), the forecast data is always static (table with FCST data only), so I was able to get this Linearity/attainment data with the total sales, but I haven't been able to understand how to create a net sales number, the calculation will be easy to understand with the tables below:

I was able to create something similar but the net new attainment linearity just doesn't work, Any thoughts of what I can do differently?
If I could create a day1 sales column that is static with the sales day 1 numbers and be able to deduct that from every new date, I think would work.

1 ACCEPTED SOLUTION
Community Support

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

4 REPLIES 4
Community Support

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Helper II

this works perfectly, thank you very much

Super User

@Chava1881 ,  a measure =

New Sales =

Sum(Table[Sales]) - Calculate(Firstnonblankvalues(Table[Capture_date], Sum(Table[Sales]) ), allexcept(Table, Table [Capture_date]) )

new column

new sales = [sales] - minx(filter(Table, Table[Capture Date] = min( Table[Capture Date] ) ),[Sales])

Helper II

The first one did not work, and the second one is giving me an issue, as there are products within the data set, it takes the min value of the date, so it reduces in 4 everything, I need it for the total QTY of the day 1.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors