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

[jimplum01]

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.

[jimplum01]

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]

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

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

[jimplum01]

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]

@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,

[jimplum01]

Thank you for looking at this.

The results of the DAX expression you provided are the same as what I have had, but, I will dig in a bit more on the structure of what you suggested to understand the nesting methods on how you filtered.

The problem remanins that the Value of the Measure should be $0 in your example when IsWorkDay=0.

If it was SQL, it would be something like SUM(Sales.RevenuePerDay)*CalendarDate.IsWorkDay, but that kind of expression I cannot get to work in DAX.

[v-sihou-msft]

@jimplum01

In this scenario, we can build a calculated table to build that measure into a column. Then add a calculated column and apply condition with IF statement.

Table = ADDCOLUMNS(CalendarDate,"RevenuePerDay",[Measure])

RevenuePerBillingDay = IF('Table'[IsWorkDay]=1,'Table'[RevenuePerDay],BLANK())

Regards,

[jimplum01]

Your input was helpful, and based on this I have come up with the following (since I need to be able to slice by SalesPipelineID as well. I need to rename my table 'Calendar' to aviod any confusion with the Calendar funcion.

BillingForecast = 
ADDCOLUMNS(
  CROSSJOIN(
    SUMMARIZE('Sales', [SalesPipelineID]) ,
    SUMMARIZE('Calendar',[CalendarDate],[WorkDay])
  ),  
  "RevenuePerDay",[Measure]*[WorkDay]  
)

I am going to do a bit more testing with some scenarios to see if this is the desired result for all cases.

Is there a benefit to using Blank() instead of 0 for days that should not count revenue?

Thank you