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 Calend...
Need Help
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.

       

       

      • v-sihou-msft's avatar
        v-sihou-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        jimplum01

         

        In your scenario, you have created the "BillingDays" and "RevenuePerBillingDay" based on the CalendarDate table. Now you need to limit the Sales table context if the CalendarDate date values is in date range of the Sales row context.

         

        I assume you have Sales table below:

         

         

        Then you should filter the CalendarDate date values to determine if rows in Sales table need to be included. The formula of the measure should be like below:

         

        Measure = 
	CALCULATE(SUM(Sales[RevenuePerBillingDay]),
		FILTER(Sales,
			COUNTROWS(
				FILTER(
					VALUES(CalendarDate[Date]),
			            Sales[StartDate]<=CalendarDate[Date] &&
			            Sales[EndDate]>=CalendarDate[Date])
			)>0
			)
		)

        And if a date appears in multiple date ranges, the corresponding "RevenuePerBillingDay" will aggregate.

         

         

         

         

         

         

        Regards,