Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chava1881
Helper II
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:

Chava1881_0-1660859889447.png

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. 

Chava1881_1-1660859937072.png


Sample PBIX here

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @Chava1881 

 

Is this your expected output?

vcgaomsft_0-1661249407603.png

vcgaomsft_1-1661249528027.png

 

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

View solution in original post

4 REPLIES 4
v-cgao-msft
Community Support
Community Support

Hi @Chava1881 

 

Is this your expected output?

vcgaomsft_0-1661249407603.png

vcgaomsft_1-1661249528027.png

 

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

this works perfectly, thank you very much

amitchandak
Super User
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])

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.