Formula help: Count weeks based upon field of record

[justingraff88]

I have a utilization report where I am calculating a billable revenue target for an individual based upon their hourly cost rate then multiplying by 36 hrs and the number of weeks in a date table [wk end] based upon the slicers selected.

Current formula:

Target Revenue = sum(Contact[Cost Rate])*36*DISTINCTCOUNT('Calendar Tables'[Wk End])

I also have a [start date] and [end date] (end date is blank if still employed) for each contact record and I am trying to figure out how to add those into the formula to further filter the Calendar Tables so that it will only count weeks that are in between those dates (when the employee was actually employed.

Here is my current data model. Contact and Calendar Tables are not currently related.

[tamerj1]

Hi @justingraff88 

what is the rule of the Timecard Table. And which column(s) is/are invloved in the relationships with the other two tables?

[justingraff88]

The timecard table is where the actual revenue is recorded. In the current model the Calendar Table filters the date and the contact filters the employee in the Timecard table.

[tamerj1]

What are you slicing by? Or this is just a card visual?

[justingraff88]

I have the data being displayed in a matrix and then several slicers on the left. All the values (columns) in the matrix are from the timecard table. The home team slicer is from the contact table.

[tamerj1 · Accepted solution]

Hi @justingraff88 
Apologies for the late reply. I was extra busy the past couple of days. 
Looking at your report, I would advise you to slice by the "Resource" column from the 'Contact' dim table and not from the 'Timecard' fact table. Otherwise the SUM ( Contact[Cost Rate] ) will not be filtered and will show the overall value in all rows of the visual. Fact do not filter Dim but Dim filters Fact.
Then you can use the following code to calculate the target revenue. I hope it works

Target Revenue =
VAR WeeklyCost =
    SUM ( Contact[Cost Rate] ) * 36
VAR MinSelectedDate =
    MIN ( 'Calendar Tables'[Date] )
VAR MaxSelectedDate =
    MAX ( 'Calendar Tables'[Date] )
VAR StartDate =
    MIN ( Contact[pse__Start_Date__c] )
VAR EndDate =
    MAX ( Contact[pse__Last_Date__c] )
VAR EndDateAdjusted =
    IF ( ISBLANK ( EndDate ), MaxSelectedDate, EndDate )
VAR RequiredDates =
    INTERSECT (
        CALENDAR ( StartDate, EndDateAdjusted ),
        CALENDAR ( MinSelectedDate, MaxSelectedDate )
    )
VAR NumberOfWeeks =
    ROUNDDOWN ( DIVIDE ( COUNTROWS ( RequiredDates ), 7 ), 0 )
RETURN
    WeeklyCost * NumberOfWeeks