Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Daily Sales Target

Hi all!   I'm a new user in Power BI and have what I believe is a fairly easy-solved question. I'm trying to display a daily sales target for the rest of the year based on YTD sales and the set yea...
  • PaulDBrown's avatar
    5 years ago

    Anonymous 

    To calculate the working days remaining until the end of year, you can try:

    1) Include a column in your calendar table to identify working days (in my case working days exclude Saturdays and Sundays, but adjust according to your setting):

    2) Create a simple SUM measure for working days: 

     

    Sum Working days = SUM('Calendar Table'[Working Days])

     

     

    3) Calculate the working days remaining until the end of the current year:

     

    Remaining Working Days Measure = 
    VAR _EndOfYear = DATE(YEAR(TODAY()), 12, 31)
    
    RETURN
    CALCULATE([Sum Working days], 
            REMOVEFILTERS(), 
                FILTER(ALL('Calendar Table'[Date]), 
                    'Calendar Table'[Date] > TODAY() 
                        && 'Calendar Table'[Date] <=_EndOfYear))

     

     

    Now you can divide the remaing sales target by the remaining working days

     

    PS If you wish to calculate the remaining working days (Monday - Firday) using only a measure (without including a column in the Calendar Table), you can use:

    Measure Remaing Working Days = 
    VAR EOYEAR = DATE(YEAR(TODAY()), 12, 31)
    RETURN
    COUNTROWS(
        FILTER(ALL('Calendar Table'[Date]), 
            'Calendar Table'[Date] > TODAY()
                && 'Calendar Table'[Date] <= EOYEAR
                    && WEEKDAY('Calendar Table'[Date], 2) IN {1,2,3,4,5}))