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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Tuan
Helper III
Helper III

Single Gross Sale Number to Weekly Sales

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?

1 ACCEPTED SOLUTION

Hi,

 

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

 

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

 

Starting Data

Channel LeadAccount OwnerAccount NameShip TimingF18 Gross $
ICTuanAMPM8/1/2018129851
ICTuanCircle K - Arizona10/21/201884324
ICTuanCircle K - West Coast2/26/201866515
ICJohnMapco - Delek4/2/2018119976

 

End Result

Channel LeadAccount OwnerAccount NameWeekly Ship TimingWeekF18 Gross $
ICTuanAMPM8/1/2018Week 315902
ICTuanAMPM8/8/2018Week 325902
ICTuanAMPM8/15/2018Week 335902
ICTuanAMPM8/22/2018Week 345902
ICTuanAMPM8/29/2018Week 355902
ICTuanAMPM9/5/2018Week 365902
ICTuanAMPM9/12/2018Week 375902
ICTuanAMPM9/19/2018Week 385902
ICTuanAMPM9/26/2018Week 395902
ICTuanAMPM10/3/2018Week 405902
ICTuanAMPM10/10/2018Week 415902
ICTuanAMPM10/17/2018Week 425902
ICTuanAMPM10/24/2018Week 435902
ICTuanAMPM10/31/2018Week 445902
ICTuanAMPM11/7/2018Week 455902
ICTuanAMPM11/14/2018Week 465902
ICTuanAMPM11/21/2018Week 475902
ICTuanAMPM11/28/2018Week 485902
ICTuanAMPM12/5/2018Week 495902
ICTuanAMPM12/12/2018Week 505902
ICTuanAMPM12/19/2018Week 515902
ICTuanAMPM12/26/2018Week 525902
ICTuanCircle K - Arizona10/21/2018Week 438432.4
ICTuanCircle K - Arizona10/28/2018Week 448432.4
ICTuanCircle K - Arizona11/4/2018Week 458432.4
ICTuanCircle K - Arizona11/11/2018Week 468432.4
ICTuanCircle K - Arizona11/18/2018Week 478432.4
ICTuanCircle K - Arizona11/25/2018Week 488432.4
ICTuanCircle K - Arizona12/2/2018Week 498432.4
ICTuanCircle K - Arizona12/9/2018Week 508432.4
ICTuanCircle K - Arizona12/16/2018Week 518432.4
ICTuanCircle K - Arizona12/23/2018Week 528432.4

Hi,

 

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

 

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

Thank you again!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Capture.PNG

Hi,

 

Try this

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.