Daily Sales Run Rate/Projection Measure

[ukhan2020TO]

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/number of days of sales*number of days in month). The data is pretty straightforward and Calendar table is already linked to sales data table. Any help is much appreciated.

[amitchandak]

@ukhan2020TO , With help from a date Table.

You may have do +/- one date diff. so check that seprataely

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

MTD Date = 
Var  _max = CALCULATE(SUM(Sales[Date]),DATESMTD('Date'[Date]))
var  _eod = EOD(_max,0)
var  _start = EOD(_max,-1)+1
return  divide([MTD Sales], datediff(_start,_max,Day))*datediff(_start,_eod,Day)

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.

[ukhan2020TO]

Hi Amit,

Is "eod" coming from a date table....see my response in the thread with more detailed data and description of what i am looking for...

Thank you!

[v-easonf-msft · Accepted solution]

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]

@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]

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 Days	Day of Month
334,416.78	1
79.57	3
313,449.04	4
37,876.94	5
274,971.36	6
455,789.78	7
534,518.28	8
689,975.05	11
270,497.72	12
331,275.68	13
461,502.66	14
233,845.32	15
5,379.90	16
660.26	17
184,182.79	18
256,112.80	19
473,647.46	20
438,192.11	21
226,193.46	22
1,539.65	24

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.