Distributing Effort Hours Between Two Dates for Graphing

[Plaferriere]

I want to evenly distribute "Total Planned Efforts" by "distPID" (task) between "start" and "end" dates.  The purpose of which is to see the total sum of "Total Planned Efforts" by any given period (Year, Month, Week, Day).


Tasks can last any duration (from Days to Years) - I dynamically generate a complete calendar between MinStart and MaxEnd.

The goal is to visualize "Efforts by period".  The design needs to ensure that we don't have to re-code columns, queries, etc because dates are different (many solutions have users hard-coding in Month columns, this won't work).

My source data arrives like this "Project Estimates Tables".  

With this table I should be able to determine when the distribtutions of estimated hours (by duration) should begin and end.

I'm stuck trying to distribute the "maxEstHours" across the Calendar's 'available dates, based on the Start and End times in the table above. 

As you can see, efforts are not being distributed into other periods.- 01030 for example lasts all year and "Summed daily efforts" should appear in every month.

I'd like to manage this "at the daily level" and use Timelines etc to group periods together.

[Plaferriere]

Thank you so much for your assistance.  This is a huge improvement, and I cannot be more grateful.  If I can impose further, I need to see this at a weekly level but will only work if I also add the [Day] along with the [Month], which obviously breaks the Yearly overview.    Is there any way to have the best of both worlds?

Yearly Comparisons (With [Day])

vs without

Weekly Comparisonss (With [Day])

vs without

[BA_Pete]

Ok. This is an axis issue rather than a calculation issue.

I would probably suggest either:

-1- Putting calendar[day], ~[week], ~[month], ~[year] into the axis, or into a field parameter, to allow the end user to explicitly select the axis granularity, or;

-2- Put calendar[day] into the axis, then change your chart x-axis to the Continuous axis type.

This choice will ultimately be down to end user preference, but the measure should work fine using either option.

Pete

[BA_Pete · Accepted solution]

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