Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi, @ukhan2020TO
Here is a sample .
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
@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.
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!
Hi, @ukhan2020TO
Here is a sample .
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
@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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |