Forum Discussion

lrdub's avatar
lrdub
Frequent Visitor
7 years ago
Solved

Sales Projection

 

EOM Projection
I know the solution to my problem would likely be very simple but I'm thinking too complicated.
 
My Sales team need to monitor the current month's projected sales as compared to previous months.
 
I hope to do the following;
  1. Say our total sales so far this month are $100
  2. 15 Days of the month have passed (it is the 15th)
  3. Average sales per day is $6.67
  4. If it is a 31 day month (March), projected sales figure I want should be $206.77
How do I return the following?
Month    Sales
Jan         $209.00    (Historic)
Feb        $186.06    (Historic)
March    $206.77    (Current Month Projected Sales)
 
Thanks In advance!!
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi lrdub,

     

    I made one sample for your reference, If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Here we can create a measure to achieve your goal.

     

    Measure = var currentmonth = FORMAT(TODAY(),"mmmm")
var previsousum = CALCULATE(SUM(Table1[sales]),FILTER(ALL(Table1),Table1[Month]<>currentmonth))
var countr = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Month]<>currentmonth))
return 
IF(MAX(Table1[Month])<>currentmonth,SUM(Table1[sales]),(previsousum/countr)*COUNTROWS(Table1))

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi lrdub,

     

    I made one sample for your reference, If it doesn't meet your requirement, kindly share your sample data and excepted result to me. Here we can create a measure to achieve your goal.

     

    Measure = var currentmonth = FORMAT(TODAY(),"mmmm")
var previsousum = CALCULATE(SUM(Table1[sales]),FILTER(ALL(Table1),Table1[Month]<>currentmonth))
var countr = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Month]<>currentmonth))
return 
IF(MAX(Table1[Month])<>currentmonth,SUM(Table1[sales]),(previsousum/countr)*COUNTROWS(Table1))

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi lrdub,

       

      Does that make sense? If so, kindly mark my answer as a solution to close the case.

       

      Regards,
      Frank