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

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