Calculate Revenue per month based off number of hours

[LyonsBI_BRL]

I'm currently working through a problem that will give me the correct RevenuePerMonth based off the number of workable hours and workdays each month. Each person has a [dateBegin] and a [dateEnd] for when they will complete their work with the company. Below, the formula gives me the revenue generate for that person for the remaining months that they have.

Computed Rev = VAR _Billablehours = [Forecast Workable Hours]
VAR _ClientBillRate = [clientBillRate]
VAR _CurrentMonth = DATE(YEAR(TODAY()), MONTH(TODAY()),1)
VAR EndDate = [dateEnd]
VAR BeginDate = [dateBegin]
VAR NumberWorkDays = CALCULATE( COUNTROWS('Date'), DATESBETWEEN('Date'[Date], _CurrentMonth, EndDate ), 'Date'[Weekday] = TRUE || 'Date'[Public Holidays] <> 0)

RETURN

IF ( ISBLANK (NumberWorkDays), 0, NumberWorkDays) * [hoursPerDay] * _ClientBillRate

Now in order ot find the number of workable hours, I use the following calculated formula below. Which gives me both my number of billable hours and/or billable days excluding weekends and holidays.

Forecast Workable Hours = 
VAR _MonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()),1)
VAR _MonthEnd = EOMONTH ( _MonthStart , 12 )
VAR _dateBegin = [dateBegin]
VAR _dateEnd = [dateEnd]
VAR _StartDate = IF ( _dateBegin >= _MonthStart , _dateBegin , _MonthStart )
VAR _EndDate = IF ( _dateEnd <= _MonthEnd , _dateEnd , _MonthEnd )
VAR _NumberWorkDays = CALCULATE( COUNTROWS('Date'), DATESBETWEEN('Date'[Date], _StartDate, _EndDate), 'Date'[Weekday] = TRUE || 'Date'[Public Holidays] <> 0)

RETURN 

IF ( ISBLANK (_NumberWorkDays), 0, _NumberWorkDays) * [hoursPerDay]

My problem is, I'm needing to calculate for each row, the revenue generated each month based off the number of workable days and/or hours for that person based off their billable rate. The formulate below though, unfortunately what it's doing is it's dividing up the remaining number of months for that user and dividing it, giving me the same revenue every month which isn't what I want as some months have more or less workable days / or hours.

RevenuePerMonth = 'Revenue'[Computed Rev]/(DATEDIFF(DATE(YEAR(TODAY()), MONTH(TODAY()),1), 'Revenue'[dateEnd],MONTH)+1)

So instead of it looking like this

It needs to look more like this

I know it's possible just I need some help on how to write this in DAX. Any help would be greatly appreciated. 

Thanks!

[v-jingzhang]

Hi @LyonsBI_BRL 

From your second image, RevenueforMonth = Workable Hours * BillRate. While in the first image, the [Forecast Workable Hours] returns the same 1400 value in each row. Is it getting the correct result? If [Forecast Workable Hours] already returns the correct result, why not creating a calculated column using [Forecast Workable Hours] * [clientBillRate] to get the expected result?

Best Regards,
Community Support Team _ Jing

[VijayP]

@LyonsBI_BRL 

you must have that column in your Datedim Table. Create one and then you will not get this error

[VijayP]

@LyonsBI_BRL 

CALCULATE('Revenue'[Computed Rev]/(DATEDIFF(DATE(YEAR(TODAY()), MONTH(TODAY()),1), 'Revenue'[dateEnd],MONTH)+1),
FILTER(ALL(DATESdim),VALUES(Month &YEar))

Try this

[LyonsBI_BRL]

Doesn't seem to be working or is Values looking for a column?