DAX PowerPivot query functions to spread aggregated values over time period

Question

How do I split/chunk the pre-summed value for long-running tasks to match the time interval I’m looking for?

Any ideas are greatly appreciated! I’m open to doing some additional ETL/data creation as a PowerQuery step, but would really like to figure out the right DAX expression for this so it can be something available as a time-slicer/filter on the project.

Answer 1

The solution below assumes a table called 'data' that has 4 columns: id, start, end, value, and table called calendar that has 2 columns Date and Month.

Measure 1: Sum the hours

[Hours] =SUM(Data[Value])

Measure 2: Apply the hours to the dates and divide by the number of dates

[Hours Apportioned Raw] =
 CALCULATE ([Hours],
FILTER (
    Data,
    Data[Start] <= MAX ( Calendar[Date] )
        && Data[END] >= MAX ( Calendar[Date] )
       )
            )
/ ( MAX ( Data[End] ) - MAX ( Data[Start] ) )

Measure 3: Iterate Measure 2 over dates and ids to give correct values

=
    SUMX (
        VALUES ( Calendar[Date] ),
        SUMX ( VALUES ( Data[ID] ), [Hours Apportioned RAW] )
    )

Hope this makes some sense.