Forum Discussion

jimplum01's avatar
jimplum01
Frequent Visitor
9 years ago

Need Help: Forecast Revenue between start and end date excluding non-working days

For each service sale that will be delivered to customers over the contract period I want to evenly distrubute the total sale over the working days from the startdate to the end date. I have a Calendar table that defines all dates, and a Sales table that includes the Revenue, Start Date, End Date. I added a computed column BillingDays for the number of days excluding holidays and weekends. I added a computed column to set the revenue for each billing day. I also want a Measure that can be used for the displaying the revenue over the course of the contract from Start Date through End Date.

 

BillingDays = CALCULATE(COUNT('Calendar'[WorkDay]),
           DATESBETWEEN('Calendar'[CalendarDate], 
                        'Sales'[StartDate],
                        'Sales'[EndDate]
                       ), 'Calendar'[WorkDay] = 1
          )

RevenuePerBillingDay = DIVIDE(Sales[Revenue],Sales[BillingDays],0)

Measure = CALCULATE(
 SUM(Sales[RevenuePerBillingDay]),
 FILTER ('Calendar','Calendar'[WorkDay]=1),
 FILTER ('Sales','Sales'[StartDate] <= CALCULATE ( MAX ( 'Calendar'[CalendarDate] ) ) ),
 FILTER ( 'Sales', 'Sales'[EndDate] >= CALCULATE ( MIN ( 'Calendar'[CalendarDate] ) ) )
)

A few problems:

  1. The filter to just include WorkDays does not do anything. 
  2. If a non-work day is included (WorkDay = 0), I would want the Measure for that day to be $0.
  3. The total of the Measure from StartDate to EndDate adds up to $16 instead of  $1000, (because $15.625 * 64 = $1000).

Sales DataNon-Work Days Have Value for Measure

 

Dec 2,3 should have $0 Revenue (as they are not work days)

Thank you for assistance.

Need Help

7 Replies

  • jimplum01's avatar
    jimplum01
    Frequent Visitor
    9 years ago

    The Key issue is to see that when WorkDay=0, the Measure for that day is $0. 

     

    Would anyone have ideas on how to achieve that?

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      9 years ago

      Can you add another measure called Measure1 and use that in the graph:

       

      Measure1 = if(WorkDays = 0, BLANK(), MEASURE)

       

      • jimplum01's avatar
        jimplum01
        Frequent Visitor
        9 years ago

        Similar issue with trying to calculate this way:

         

        SUM(Sales[RevenuePerBillingDay])*'Calendar'[WorkDay]

         

        Results in the following error message:

         

        A single value for column 'WorkDay' in table 'Calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.