Distributing Effort Hours Between Two Dates for Graphing

Hi Plaferriere ,

There's two ways to do this, one in Power Query, one in a DAX measure.

The PQ way expands your table into a row per day and divides the total over each row. It works great and makes subsequent calculations very easy, but it does create a HOOOJ table, so I'll stick with the DAX option for now and see how you get on with that.

Try something along these lines as a MEASURE:

_dailyValueOT = 
VAR __cDate = MAX(calendar[date])
VAR __dailyVal =
CALCULATE(
    SUMX(
        SUMMARIZE(												
            FILTER(    // Create base crossjoin between summary val and days
                CROSSJOIN(projectEsts, calendar),
                calendar[Date] >= projectEsts[minStart]
                && calendar[Date] <= projectEsts[maxEnd]
            ),
            projectEsts[distPID],    // Then summarise keeping summary val and needed dims
            calendar[Date],
            projectEsts[maxEstHours],
            projectEsts[minStart],
            projectEsts[maxEnd]
        ),
        DIVIDE(	   // Apply split at row-level with iterator
            projectEsts[maxEstHours],
            DATEDIFF(
                projectEsts[minStart],
                projectEsts[maxEnd],
                DAY
            ) + 1
        )
    )
)
RETURN
IF(__cDate <= MAX(projectEsts[maxEnd]), __dailyVal)

Add this into your matrix making sure to use calendar[Month] for the columns and it should give you what you're after.

Pete