Single Gross Sale Number to Weekly Sales

[Tuan]

I'm currently working with sales data in salesforce. Is it possible to turn a estimated Annual sale number into weekly sales through a year.

For instance if I have sales starting on 5/1/18 and estimated annual sales is 100k can I somehow create weekly sales from that date adding up to the end of the year to 100k?

[Greg_Deckler]

Probably, it will likely involve WEEKNUM. But, sample data and expected output would be very helpful.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Tuan]

Here's an example. I'm taking data from salesforce.

Starting Data

Channel Lead	Account Owner	Account Name	Ship Timing	F18 Gross $
IC	Tuan	AMPM	8/1/2018	129851
IC	Tuan	Circle K - Arizona	10/21/2018	84324
IC	Tuan	Circle K - West Coast	2/26/2018	66515
IC	John	Mapco - Delek	4/2/2018	119976

End Result

Channel Lead	Account Owner	Account Name	Weekly Ship Timing	Week	F18 Gross $
IC	Tuan	AMPM	8/1/2018	Week 31	5902
IC	Tuan	AMPM	8/8/2018	Week 32	5902
IC	Tuan	AMPM	8/15/2018	Week 33	5902
IC	Tuan	AMPM	8/22/2018	Week 34	5902
IC	Tuan	AMPM	8/29/2018	Week 35	5902
IC	Tuan	AMPM	9/5/2018	Week 36	5902
IC	Tuan	AMPM	9/12/2018	Week 37	5902
IC	Tuan	AMPM	9/19/2018	Week 38	5902
IC	Tuan	AMPM	9/26/2018	Week 39	5902
IC	Tuan	AMPM	10/3/2018	Week 40	5902
IC	Tuan	AMPM	10/10/2018	Week 41	5902
IC	Tuan	AMPM	10/17/2018	Week 42	5902
IC	Tuan	AMPM	10/24/2018	Week 43	5902
IC	Tuan	AMPM	10/31/2018	Week 44	5902
IC	Tuan	AMPM	11/7/2018	Week 45	5902
IC	Tuan	AMPM	11/14/2018	Week 46	5902
IC	Tuan	AMPM	11/21/2018	Week 47	5902
IC	Tuan	AMPM	11/28/2018	Week 48	5902
IC	Tuan	AMPM	12/5/2018	Week 49	5902
IC	Tuan	AMPM	12/12/2018	Week 50	5902
IC	Tuan	AMPM	12/19/2018	Week 51	5902
IC	Tuan	AMPM	12/26/2018	Week 52	5902
IC	Tuan	Circle K - Arizona	10/21/2018	Week 43	8432.4
IC	Tuan	Circle K - Arizona	10/28/2018	Week 44	8432.4
IC	Tuan	Circle K - Arizona	11/4/2018	Week 45	8432.4
IC	Tuan	Circle K - Arizona	11/11/2018	Week 46	8432.4
IC	Tuan	Circle K - Arizona	11/18/2018	Week 47	8432.4
IC	Tuan	Circle K - Arizona	11/25/2018	Week 48	8432.4
IC	Tuan	Circle K - Arizona	12/2/2018	Week 49	8432.4
IC	Tuan	Circle K - Arizona	12/9/2018	Week 50	8432.4
IC	Tuan	Circle K - Arizona	12/16/2018	Week 51	8432.4
IC	Tuan	Circle K - Arizona	12/23/2018	Week 52	8432.4

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution here.  Hope this helps.

[Tuan]

Thank you so much, still relatively new to this but will go through the file and figure out how everything works.

Thank you again!

[Ashish_Mathur]

You are welcome.

[Tuan]

Been working with the file. How did you add all the extra rows?

[Ashish_Mathur]

Hi,

In the Query Editor, i exploded the date range into one row per date by using this Custom formula

={Number.From(Start)..Number.From(End)}

Check the steps in the Query Editor.

[Tuan]

Can't seem to get the first week to get the correct min number. It should be based off of the "Opportunity Name" column.

Attached is a image

[Ashish_Mathur]

Hi,

Try this

=CALCULATE(MIN('Pipeline Recap'[Week Number]),ALL('Pipeline Recap'[Opportunity Name]))