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

     

     

amitchandak's avatar
amitchandak
Super User
6 years ago

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.

ukhan2020TO's avatar
ukhan2020TO
Frequent Visitor
6 years ago

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's avatar
    v-easonf-msft
    Community Support
    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