Multiply Weekly Hours by Factor with Date Range

[Anonymous]

Hey all,

Working on capacity planning using Power BI. Everything worked out well when creating relationship between 'Roster' and 'Capacity Target' tables between a join of "Tier Level" and "Team" and briniging over "Target Prod Rate" using the Related DAX function to the individuals in the 'Roster' file.

Here are the measures written for targetted output and to account for start and term dates in the roster.

Cases Expected = ROUND(([Total Weekly Hours]*1.09)*[Target Prod Rate],0)

Cases Expected Filtered =
CALCULATE (
    [Cases Expected],
    FILTER (
        Roster,
        (
            Roster[Start Date] <= FIRSTDATE ( DateDim[Date] )
                && Roster[Term Date] >= LASTDATE ( DateDim[Date] )
        )
            || (
                ISBLANK ( Roster[Term Date] )
                    && Roster[Start Date] <= FIRSTDATE ( DateDim[Date] )
            )
    )
)

However, I want to evolve the model to account for an optimistic outlook in the future, where we see an increase in target production rate in the future. So I thought it would make sense to add in start and expiry dates to the "Target Production Rate" but in doing so, I was only able to create a relationship with a bridge table. Because of using the bridge, I'm not able to use the related function to bring over the "Target Production Rate" to the roster file thus breaking all the measures above...

That's where I'm stuck at, any help/input would be immensely appreciated!

[v-juanli-msft]

Hi @Anonymous 

"Target Prod Rate", “Cases Expected”, “Cases Expected Filtered” are measures and created in 'Capacity Target' table,

'Roster' and 'Capacity Target' tables have a relationship one ('Roster') to many ('Capacity Target'),

Their relationship is based on a combination of two columns "Tier Level" and "Team".

Is my understanding correct?

If not, please show a simple example of your tables and structure.

an increase in target production rate,

add in start and expiry dates to the "Target Production Rate”

I’m not clear about your requirement, how do you create the "Target Production Rate” measure before?

What result would you like finally?

Best Regards
Maggie

[Anonymous]

Hey Maggie,

Those measures are created on a standalone table used to house measures only. The "Target Prod Rate" is a measure off the 'Roster' table that has a relationship to the 'Capacity Target' as you stated, on "Tier Level" and "Team."

• I'm using the relationship to create a column using the related function, to bring in the 'Capacity Target'[Production Rate] as the 'Roster'[Target Prod Rate] at the analyst level in the 'Roster' table.
• Currently:
  • Columns are Dates Hierarchy (Year > Month > Week Start) from a 'Date Dimension' Table
  • Rows are Team, Tier, Analyst from the 'Roster' Table
  • Value is a Measure "Cases Expected Filtered" which accounts for the Analyst Start and Term date from the roster file in so targets can start and stop accordingly.
    • Cases Expected =
      ROUND ( ( [Total Weekly Hours] * 1.09 ) * [Target Prod Rate], 0 )
    • Cases Expected Filtered =
      CALCULATE (
      [Cases Expected],
      FILTER (
      Roster,
      ( Roster[Start Date] <= DATEADD(FIRSTDATE( DateDim[Date] ), -15 , DAY) && Roster[Term Date] >= LASTDATE ( DateDim[Date] ) ) ||
      ( ISBLANK(Roster[Term Date]) && Roster[Start Date] <= DATEADD(FIRSTDATE( DateDim[Date] ) , -15 , DAY ) )
      )
      )

What I want to get to from all of this, is a way factor an increase to Production rate at different point in time. For example, the back half of the year would have higher targets if their production rate for 1/1/19 - 6/31/19 was 1, and for 7/1/19-12/31/19 was 1.75. 

I was thinking adding the effective dates for the [Target Rates] in the 'Capacity Target' Table, but that would mean I would need to create a bridge between 'Capacity Targets' and 'Roster' which would break the way I'm writing these measures. Any alternatives? 

Thank you so much for helping me work through this, look forward to replies! Let me know if I'm clear, and I can try to clear any PII and post a PBI file.