Help With Calculation

Rob F

Hello Everyone,

I'm hoping you can help me figure out what I'm missing with a calculation I've been trying to build.

The Need:

We're trying to calculate the average number of days between promotions for our workforce.

We'd want the number of days as of the last day of the analysis period but an average overall. I imagine this would be similar to the delivered Time Between Promotions but I can't see what was done in the semantic model for delivered facts.

The Conceits:

I know there's a delivered calculation for this, but it won't work for our usage.

We count promotions from Legal Entity Transfers which aren't being counted by default. Not to mention, it appears that their previous promotion date doesn't carry forward when they've moved to a new Work Relationship.

We've added an extension to the assignment details which provides the last promotion date for the worker while they've been active. This extension works and will return a null value until their first promotion.

The attempts

We've tried to tackle this from a few angles but it usually comes to a calculation like this: (formatted for readability)

AVG(
  TIMESTAMPDIFF(SQL_TSI_DAY,
      IFNULL("Core"."Dim - Assignment Details"."Most Recent Promotion Date", 
             "Core"."Dim - Assignment Details"."Enterprise Seniority Date"),
  "Core"."Fact - Assignment"."Snapshot Calendar Date" )
)

That formula, when applied in the semantic model. Will produce a result that looks correct.

Unfortunately it doesn't allow us to see it over the span of time.

I've tried using the same formula without the AVG calculation, but it then gets treated as an attribute instead of a fact which prevents me from applying a column level aggregation on it.

Call to action

What am I missing? How can I get this measure to work as an average by whichever attributes we're measuring?