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.