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

jimplum01's avatar
jimplum01
Frequent Visitor
9 years ago

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's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      jimplum01
      Frequent Visitor
      9 years ago

      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