Forum Discussion

ukhan2020TO's avatar
ukhan2020TO
Frequent Visitor
6 years ago
Solved

Daily Sales Run Rate/Projection Measure

Hello,   I need to create a measure where by the formula takes my daily sales and then gives me a projection until the end of the month based on the remaining days left in the month e.g. (MTD sales...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi, ukhan2020TO 

     

    Here is a sample .

    pbxi attached 

     

    Try measures as below if it works:

    Today = DATE(2020,05,24)
    _bussiness_days = CALCULATE(COUNT('Table'[Date]),FILTER('Table',WEEKDAY('Table'[Date],2)<=5))
    _fact_bussiness_days = 
CALCULATE (
    COUNT ( 'Date'[Date] ),
    FILTER (
        'Date',
        ( WEEKDAY ( 'Date'[Date], 2 ) <= 5 )
            && 'Date'[Date] >= DATE ( YEAR ( [Today] ), MONTH ( [Today] ), 1 )
            && 'Date'[Date]
                <= DATE ( YEAR ( [Today] ), MONTH ( [Today] ) + 1, 1 ) - 1
    )
)
    result = SUM('Table'[Daily Days])/[_bussiness_days]*[_fact_bussiness_days]

     

    Best Regards,
    Community Support Team _ Eason

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

ukhan2020TO  - Seems like you would need a date column in there so just going to assume you have one.

 

Maybe something along the lines of:

 

Measure =
  VAR __Date = MAX('Table'[Date])
  VAR __Month = MONTH(__Date)
  VAR __Year = YEAR(__Date)
  VAR __Table = FILTER('Table',MONTH('Table'[Date]) = __Month && YEAR('Table'[Date]) = __Year)
  VAR __DaysInMonth = ( EOMONTH(__Date,0) - DATE(YEAR(__Date),MONTH(__Date),1) ) * 1.
RETURN
  AVERAGEX(__Table,[Total Sales]) * __DaysInMonth

 

But really, this is a pure guess because the information provided is spotty. 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

 

  • ukhan2020TO's avatar
    ukhan2020TO
    Frequent Visitor
    6 years ago

    Hi Greg,

     

    Apologies for the spotty data. Im a newbie to PowerBi. Let me try this again...

     

    Required: Sales Run Rate for the Month using Daily Sales to date

     

    Example:  May: I have daily sales up May 24, 2020 in the table below. 

    What Output Should be: Total Sales MTD = $5,524,106.61. Number of Business Days Until 24th = 16. Therefore, run rate should be = (Totals Sales MTD/16 business Days) * 21 Buisness Days in May = $7,250,389.93

     

    Daily DaysDay of Month
      334,416.781
                79.573
      313,449.044
        37,876.945
      274,971.366
      455,789.787
      534,518.288
      689,975.0511
      270,497.7212
      331,275.6813
      461,502.6614
      233,845.3215
          5,379.9016
             660.2617
      184,182.7918
      256,112.8019
      473,647.4620
      438,192.1121
      226,193.4622
          1,539.6524

     

    My relationships are as follows: The date column also has a hierarchy of Year/Quarter/Month/Day

     

     

    I hope this makes it clearer....

     

    Thanks again, much appreciated.