Help with Measure to calculate Net new sales

[Chava1881]

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. 

Sample PBIX here

[v-cgao-msft · Accepted solution]

Hi @Chava1881 

Is this your expected output?

Please check the pbix file.

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

[Chava1881]

this works perfectly, thank you very much

[amitchandak]

@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])

[Chava1881]

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.