DAX Pivot and Calculations

[Anonymous]

Hello Community. I have a challenge that I need to solve for exclusively in DAX that I'm having some trouble knowing where/how to get started.

My table so far (left below) has a a job by job detail of an estimated completion date and how much is left to spend for each job. What I want to do, in the simplist form, is to spread/forecast the spend dynamically based off of the current month offset to the estimated completion date/month of the job (right below).

• For Job 1 below there's only one month of offset so I would expect all of the money to be spent in October
• For Job 6 there are 3 months of offset so I would expect that the amount Left to Spend will be spread evenly (for now) month by month until its estimated completion month of December.

This means though that as time goes on into the next month that my current month offset will change and I'll need to move the forecast dynamically into the compressed or expanded amount of months between today and whatever the current month offset is. 

I hope the illustration below helps to explain the situation better...

Again, the goal is to not go back into the PQE to do this but to do it while remaining in the model with DAX.

Thank you in advance for your input and suggetions.

-Nate

[Greg_Deckler]

@Anonymous OK, so a matrix visual with Month date and Offset in column hierarchy. Job is in rows. Then maybe:

Measure =
  VAR __Job = MAX([Job])
  VAR __DateMax = MAX([Derived ECD])
  VAR __DateMin = MIN([Derived ECD])
  VAR __Offset = MAX([DECD Month Offset])
  VAR __Table = 
    FILTER(
      ALL('Table'),
      [Job]=__Job && [Derived ECD]>=__DateMin && [Derived ECD]<=__DateMax
    )
  VAR __Amount = MAXX(__Table,[Left to Spend])
  VAR __Offset2 = MAXX(__Table,[DECD Month Offset])
RETURN
  IF(__Offset <= __Offset2, __Amount / __Offset, BLANK())

If issues @ me and post sample data as text. Thanks.